Offset formula by one row with macro
Author Message Offset formula by one row with macro

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"

Wed, 18 Jun 1902 08:00:00 GMT  Offset formula by one row with macro
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, "!")) & _
Next c
End Sub

Quote:

>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"

Wed, 18 Jun 1902 08:00:00 GMT  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.

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

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages