MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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")