Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

Change Formula on Multiple Workbooks

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jlclyde - 24 Mar 2008 21:38 GMT
Here is the macro that I have and have used to change a test folder
with test workbooks.  Not all of the sheets have the same same but
they are all sheet1.  Can you help me amend this to have it go to
sheet1 of each workbook?

Thanks,
Jay

Sub changeFormulas()
   Dim WB As Workbook
   Dim fs
   Dim i As Integer

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False

   Set fs = Application.FileSearch

   With fs
       .LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve" '
change this to the folder you want
       .Filename = "*.xls"
       If .Execute > 0 Then ' checks to see if there are excel files
in the folder specified
           For i = 1 To .FoundFiles.Count ' for each file found
               fname = .FoundFiles(i) 'sets workbook  name to be
opened
               Set WB = Workbooks.Open(fname) 'opens the file
               With WB
                   Sheet1.Range("B10") = "=IF(E5=0,0,E5/C10/C13)"
                   .Close SaveChanges:=True
               End With
           Next
       End If
   End With

   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
End Sub
Dave Peterson - 24 Mar 2008 22:18 GMT
Does this mean that you want to change the worksheet that has a codename of
Sheet1 or do you want to change the leftmost worksheet in the workbook?

If it's the leftmost worksheet, you can use:
worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

If it's the codename, you could use:

Option Explicit
Sub changeFormulas()
   Dim WB As Workbook
   Dim fs As FileSearch
   Dim i As Long
   Dim Wks As Worksheet
   Dim fName As String

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False

   Set fs = Application.FileSearch

   With fs
       ' change this to the folder you want
       .LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve"
       .LookIn = "C:\my documents\excel\test"
       .Filename = "*.xls"
       ' checks to see if there are excel files in the folder specified
       If .Execute > 0 Then
           For i = 1 To .FoundFiles.Count ' for each file found
               fName = .FoundFiles(i) 'sets workbook  name to be opened
               Set WB = Workbooks.Open(fName) 'opens the file
               With WB
                   Set Wks = Nothing
                   For Each Wks In WB.Worksheets
                       If LCase(Wks.CodeName) = LCase("sheet1") Then
                           Exit For
                       End If
                   Next Wks
                   
                   If Wks Is Nothing Then
                       MsgBox "no sheet with a codename Sheet1 in: " _
                                  & vbLf & WB.FullName
                   Else
                       Wks.Range("B10").Formula = "=IF(E5=0,0,E5/C10/C13)"
                   End If
                   
                   .Close savechanges:=Not (Wks Is Nothing)
               End With
           Next
       End If
   End With

   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
End Sub

> Here is the macro that I have and have used to change a test folder
> with test workbooks.  Not all of the sheets have the same same but
[quoted text clipped - 35 lines]
>     Application.DisplayAlerts = True
> End Sub

Signature

Dave Peterson

jlclyde - 24 Mar 2008 22:25 GMT
> Does this mean that you want to change the worksheet that has a codename of
> Sheet1 or do you want to change the leftmost worksheet in the workbook?
[quoted text clipped - 97 lines]
>
> - Show quoted text -

Dave,
    It is the one that is the left most positoin.  I was going around
with worksheets, sheet, sheets.  Thank you very much this will do the
trick nicely.
Jay
Dave Peterson - 24 Mar 2008 22:33 GMT
Oops.  I had a typo:

  .worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

That leading dot is very important.  Maybe not so in this case.  Without the
dot, the worksheets(1) reference will refer to the activeworkbook.  But since WB
was just opened, it should be active.  

I'd still add that leading dot.

> > Does this mean that you want to change the worksheet that has a codename of
> > Sheet1 or do you want to change the leftmost worksheet in the workbook?
[quoted text clipped - 103 lines]
> trick nicely.
> Jay

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.