Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
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. " wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
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. -- beechum1 ------------------------------------------------------------------------ beechum1's Profile: http://www.excelforum.com/member.php...o&userid=30489 View this thread: http://www.excelforum.com/showthread...hreadid=511437 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
How is Macro Express better than just normal Excel macros?
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
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 " wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Novice, Stupid Question, but help!
Thank you!
-Steve JMB wrote: 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 " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list - a question from novice | Excel Discussion (Misc queries) | |||
Stupid Excel operating | Excel Discussion (Misc queries) | |||
eXceL 2003 - list or combo box question | Excel Discussion (Misc queries) | |||
Question about XML functionality (add-ons?) in Excel 2000 & 2002 | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |