Excel Novice, Stupid Question, but help!

S

steverob

Ok, I've got about 200 spreadsheets with data at the top and formulas
at the bottom. All of these spreadsheets are identical in format. The
problem is that many of the formulas at the bottom were coded wrong and
I want to fix all 200 spreadsheets using a macro. The data at the top
of the spreadsheets is correct, I just need to change the formulas.
What is the best way to do this?

-Steve

PS: Sorry to ask such a basic question but I don't want to get fired on
Monday so what do I have to lose?
 
J

JMB

I think you'll need to provide some more details.

Are all 200 sheets in the same workbook? Are the formulas the same from one
sheet to the next? Are the formulas located in the same cell(s) from on
sheet to the next? What are the formulas now, and what should they be?

If the formulas are identical between sheets and located in the same cells,
you can select multiple sheets at a time by holding down the Control key and
selecting multiple sheets w/your mouse. Then, whatever you type in one sheet
will be entered in all of the sheets selected.

Before trying any suggestions - make a copy of your file. If it's really
important - make several copies.
 
B

beechum1

also do a search for macro express. it has a trial period, you can
capture or script a macro and assign a hotkey. check it out, it's
worth the 40$ if you ever have to use it outside of office as well.
 
S

steverob

Are all 200 sheets in the same workbook?

- no, each sheet is in a seperate workbook.

Are the formulas the same from one
sheet to the next?

- the formulas are the same from one *workbook* to the next

Are the formulas located in the same cell(s) from on
sheet to the next?

- the formulas are located in the same cell(s) from one *workbook* to
the next.

What are the formulas now, and what should they be?

- some are very long with a lot of if-testing.

We generate all the spreadsheets from a single template, that is why
the formulas are the same across workbooks and located in the same
place in each workbook. We could just change the template and re-create
all the spreadsheets but then our data-entry people would have to re-do
their data entry for the 200 spreadsheets.

Thanks for your help on this so far, I really appreciate it.
 
J

JMB

Here is an outline that may get you started. You will need to change the
following:

..LookIn = "Your folder's path" - I would copy all of the workbooks to a
separate folder

ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"

This puts a simple sum formula in cell A10 of the active sheet. If you want
a sheet other than the active sheet use

Worksheets("Your Worksheet Name").Range("YourRange").Formula = "YourFormula"

You'll need to include a statement like this for each formula you want
changed.
Be sure you have backups of your data before testing.



Sub Test()

With Application.FileSearch
.NewSearch
.LookIn = "I:\Test"
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), _
UpdateLinks:=0
ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"
Workbooks(ActiveWorkbook.Name).Close savechanges:=True
Next i
End With

End Sub
 
S

steverob

Thank you!

-Steve
Here is an outline that may get you started. You will need to change the
following:

.LookIn = "Your folder's path" - I would copy all of the workbooks to a
separate folder

ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"

This puts a simple sum formula in cell A10 of the active sheet. If you want
a sheet other than the active sheet use

Worksheets("Your Worksheet Name").Range("YourRange").Formula = "YourFormula"

You'll need to include a statement like this for each formula you want
changed.
Be sure you have backups of your data before testing.



Sub Test()

With Application.FileSearch
.NewSearch
.LookIn = "I:\Test"
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), _
UpdateLinks:=0
ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"
Workbooks(ActiveWorkbook.Name).Close savechanges:=True
Next i
End With

End Sub
Click to expand...
 
Top