Need to fix Formula in 700+ spreadsheets

[Divercem]

I discovered a mistake in the formula in over 700 spreadsheets. I came up with the following code to traverse the folders and put it into a spreadsheet, but I am unsure how to open each file, change the formula and insert a lookup range, save the file and close it before going on to the next file.

I get to the line Range("M49").Formula and it gives me a runtime error 1004. I tried to active the file's worksheet but that gave me another error.

Any suggestions would be appreciated as this would save me many hours.

Thanks
---------------------------------------------------------
Set fs = Application.FileSearch
With fs
.LookIn = "G:\cmills\Excel\Daily Planners" ' All the files are under the Dir
.Filename = "Daily Planner *.xls" ' All the files begin with this
.SearchSubFolders = True
If .Execute > 0 Then
' MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
DP_Row = 2
For i = 1 To .FoundFiles.Count ' For every file found do this
Filename = .FoundFiles(i)
Open Filename For Random As 1
Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP('1/2 hour lunch',N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))"
Range("N61").Value = "1 hour lunch"
Range("O61").Value = 0.0415
Range("N62").Value = "1/2 hour lunch"
Range("O62").Value = 0.0205
Range("N63").Value = "3/4 hour lunch"
Range("O63").Value = 0.031
Range("N64").Value = "No lunch"
Range("O64").Value = 0
Filename.Save
Close 1
DP_Row = DP_Row + 1
' MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

 

[PHV]

What about this ?
...
For i = 1 To .FoundFiles.Count ' For every file found do this
Filename = .FoundFiles(i)
With Workbooks.Open Filename
.Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP('1/2 hour lunch',N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))"
.Range("N61").Value = "1 hour lunch"
.Range("O61").Value = 0.0415
.Range("N62").Value = "1/2 hour lunch"
.Range("O62").Value = 0.0205
.Range("N63").Value = "3/4 hour lunch"
.Range("O63").Value = 0.031
.Range("N64").Value = "No lunch"
.Range("O64").Value = 0
.Save
.Close
End With
DP_Row = DP_Row + 1
Next i
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Divercem]

PH,

That looks like what I want it to do, but when I paste your code and run it I get a Runtime error '438' - Object doesn't support this property or method.

I'm checking the error to see if I can figure it out. If you have any other ideas, I'm all ears! )

Thanks

 

[PHV]

OOps, sorry for the typos.
...
Dim objWB As Workbook
For i = 1 To .FoundFiles.Count ' For every file found do this
Filename = .FoundFiles(i)
Set WB = Workbooks.Open(Filename)
With WB.ActiveSheet
.Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP('1/2 hour lunch',N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))"
.Range("N61").Value = "1 hour lunch"
.Range("O61").Value = 0.0415
.Range("N62").Value = "1/2 hour lunch"
.Range("O62").Value = 0.0205
.Range("N63").Value = "3/4 hour lunch"
.Range("O63").Value = 0.031
.Range("N64").Value = "No lunch"
.Range("O64").Value = 0
End With
WB.Save
WB.Close
DP_Row = DP_Row + 1
Next i
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Divercem]

PH,

That worked great except for one last thing. In the line
.Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP('1/2 hour lunch',N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))"

I need to change the '1/2 hour lunch' to "1/2 hour lunch" so the formula will work correctly. Should I concat the whole line and enclose the " in """ like this?
.Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP(" & """ & "1/2 hour lunch" & """ & ",N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))

 

[PHV]

Try this:
.Range("M49").Formula = _
"=IF(ISBLANK(N49),L49-K49-VLOOKUP(""1/2 hour lunch"",N61:O64,2,FALSE),L49-K49-VLOOKUP(N49,N61:O64,2,FALSE))"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Divercem]

Great solution PH, as you can imagine running through 700+ spreadsheets took some time to open, modify, save and close. I just had a handful that required attention due to inconsistencies in placement. But it was definitely much less time than it would have taken me to do it manually

Again, many thanks.