Hi ,can someone help .

I have a column on sheet A in which i have links to another sheet "Prices"
(put in by macro as "cell.formula=Prices!$B$3" )   i.e.

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"

Sub aaaa()
' provided every cell in the range has a formula linked to another sheet
this works
    Sub aaaa()
    ' provided every cell in the range has a formula linked to another sheet
this works
    For Each c In Range("A1:A20")
    c.Formula = Left(c.Formula, InStr(1, c.Formula, "!")) & _
        Range(c.Formula).Offset(1, 0).Address
    Next c
    End Sub

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