Offset formula by one row with macro

Quote:

> CELL A1 is "=Prices!$B$3"

> CELL A2 is "=Prices!$D$3"

> CELL A3 is "=Prices!$E$3"

> How do i write a macro to change each of th formulas in cells A1 to .....A20

> to point to the row below the present cell address each time the macro is

> run.

> i.e A1 should be "Prices!$B$4"

This may not be the best way to do it. If they are all in row 3 I would be

tempted to have a cell (TheRow) containing the current row number to be used

and have formulas

=OFFSET(Prices!$B$1,TheRow-1,0)

Then you only need to change the value in TheRow.

But to answer the question you asked:

Sub UpdateFormulas()

Dim R As Range

Dim i2ndDoll as Integer

For Each R In Range("A1:A20").Cells

i2ndDoll = InStr(InStr(R.Formula,"$")+1,R.Formula,"$")

R.Formula = Left(R.Formula,i2ndDoll) & Int(Mid(R.Formula,i2ndDoll+1))+1

Next

End Sub

Bill Manville

MVP - Microsoft Excel, Oxford, England