Variable in formula
 

I have the following formula
=AVERAGE(OFFSET(BM51,-34,0,-12,1))

Next month, I will copy it one cell to the right and adjust the -34 to -35.
Is it possible through VBA to set the "-34" as a variable when it copies the
formula, and enter the new formula with something like "MyVariable -1". In
other words have it look to see what the current value is now, copy it to the
next cell and adjust the rows accordingly.

Variable in formula
 

=AVERAGE(OFFSET(BM51,-33-COLUMN(A1),0,-12,1))

when you copy it next month the A1 will update to B1, so ,-33-COLUMN(A1),
will become ,-33-COLUMN(B1), which then evaluates to -35

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"StephanieH" wrote in message
...
I have the following formula
=AVERAGE(OFFSET(BM51,-34,0,-12,1))

Next month, I will copy it one cell to the right and adjust the -34
to -35.
Is it possible through VBA to set the "-34" as a variable when it copies
the
formula, and enter the new formula with something like "MyVariable -1".
In
other words have it look to see what the current value is now, copy it to
the
next cell and adjust the rows accordingly.

Variable in formula
 

Perfect.

Thanks.

"Bob Phillips" wrote:

=AVERAGE(OFFSET(BM51,-33-COLUMN(A1),0,-12,1))

when you copy it next month the A1 will update to B1, so ,-33-COLUMN(A1),
will become ,-33-COLUMN(B1), which then evaluates to -35

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"StephanieH" wrote in message
...
I have the following formula
=AVERAGE(OFFSET(BM51,-34,0,-12,1))

Next month, I will copy it one cell to the right and adjust the -34
to -35.
Is it possible through VBA to set the "-34" as a variable when it copies
the
formula, and enter the new formula with something like "MyVariable -1".
In
other words have it look to see what the current value is now, copy it to
the
next cell and adjust the rows accordingly.