MrExcel Publishing
Your One Stop for Excel Tips & Solutions

macro to resolve copying/pasting dilemma


Posted by Lukas C on February 15, 2001 2:41 AM

I have the following spreadsheet configuration:
A B C D
1 3 2 a1*b1 a1-.05
2 4 6 a2*b2 a2-.05
3 2 3 a3*b3 a3-.05

Columns A and B the raw data is given; C and D are computed by the macro.
The macro that I have recorded works fine for the three rows in the example. However if the spreadsheet has less or additional rows (say 2, 4 or .5), the macro still only does the computations for the first three rows shown in the example. What I wind up with looks like this:
A B C D
1 3 2 a1*b1 a1-.05
2 4 6 a2*b2 a2-.05
3 2 3 a3*b3 a3-.05
4 8 3
5 9 5
I am new to excel vba and not skilled enough to come up with a solution except to manually copy the formulas into cells c4, c5 and d4, d5. This is rather awkward since the production spreadsheets contain hundreds of rows. How can this process be fully macro controlled so that regardless of how many rows the spreadsheet contains, the formulas in columns c and d will be copied automatically.
Can anyone help, please?


Posted by KeeKee on February 15, 2001 4:12 AM

Sub do_loop()
Range("a1").Select
Do Until ActiveCell = ""
'your formula here

ActiveCell.Offset(0, 1).Range("A1").Select 'this steps down col A,
'but you have to make sure your formula takes you back to the
'beginning of the row
Loop
End Sub


Hope this helps