processing ranges in a macro
Posted by David on January 08, 2002 9:56 AM
I distribute financial statements to people outside my company, and I have to change the formulas in some ranges to values before I send them out. For example, I may have the following labels in Col A with their values in Col B:
Labor
Travel
Admin
Total Expenses
Where the formula for Total Expenses in Col B is the sum of the three rows above. Labor, Travel and, Adim are formulas would be linked to supporting calculations on other worksheets.
I need to change the formulas for Labor, Travel, & Admin to values, but leave the sum formula for Total Expenses alone. I have a number of different ranges to convert to values, and they are not uniform sizes.
I came up with the following macro:
Sub PasteValue(MyFile, MySheet, MyRange)
Workbooks(MyFile).Sheets(MySheet).Range _
(MyRange).Select
Workbooks(MyFile).Sheets(MySheet).Range _
(MyRange).Copy
Selection.PasteSpecial Paste:=xlValues
End Sub
Public Sub TestIt()
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea")
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea2")
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea25")
End Sub
Is there a cleaner way to do this? Is there an easier way to process a list of ranges than having a long list of "call" statements?
Thanks in advance from a novice macro writer.
David
Posted by Dan on January 08, 2002 10:08 AM
You should be able to use a looping statement to accomplish that. In the TestIt Sub, try something like this
Call PasteValue("TestPaste.xls","Sheet1,","TestArea")
For i = 2 to 25
Call PasteValue("TestPaste.xls","Sheet1","TestArea" & i)
Next i
You are better off also naming the first range as TestArea1 rather than TestArea, then you can include that in the loop as well:
For i = 1 to 25
Call PasteValue("TestPaste.xls","Sheet1","TestArea" & i)
Next i
HTH
Posted by David on January 09, 2002 9:43 AM
Dan, thanks.
I see the trick is in how I set up my range names; use a naming convention that I can increment, then loop through them in the macro. Much cleaner than than using a long list of "call" statements.
David
You should be able to use a looping statement to accomplish that. In the TestIt Sub, try something like this
Posted by David on January 09, 2002 9:43 AM
Dan, thanks.
I see the trick is in how I set up my range names; use a naming convention that I can increment, then loop through them in the macro. Much cleaner than than using a long list of "call" statements.
David
You should be able to use a looping statement to accomplish that. In the TestIt Sub, try something like this
Posted by Dan on January 09, 2002 9:47 AM
Exactly. Just think if you had 1000 ranges! You'd get cramps typing it out! Dan, thanks.
You should be able to use a looping statement to accomplish that. In the TestIt Sub, try something like this : Call PasteValue("TestPaste.xls","Sheet1,","TestArea")
