Posted by Chris on November 21, 2001 7:17 AM
I'm trying to create a macro that copies a formula from one row to the next, and it changes the formula depending on the row it's in.
THe formula is this:
I have sheets labelled 01-94 to 12-99 (ie Jan 94 to Dec 99)
In the summary sheet i have the dates listed in one column i.e. January 1994 to December 1999
I now want to copy the above formula next to each month but without having to manually change it myself all the way down
So in the March 1995 row, the formula would be '03-95'!C$220
in the November 1999 row the formula would be '11-99'!C$220
I have my macro set up as follows
dim year, month as integer
dim years, months as string
Range("B11").Select (this is where the formula is located)
months = month
yeats = year
for year = 94 to 99
for month = 1 to 12
if month < 10 then
months = "0" & month
ActiveCell = "='" & months & "-" & years & "'!R220C"
THIS IS WHERE I WANT THE MACRO TO THEN GO TO THE NEXT ROW
ay help would be appreciated!