Change Formula on Multiple Workbooks

[jlclyde]

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]

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

 

[jlclyde]

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










--

Dave Peterson- Hide quoted text -

- 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]

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.