Hi,

I have a worksheet where using VBA I am inserting a new row and setting
cell values
in the new row to values specified in other worksheets. I can control
the values I want to refer
to in this new row with my VBA code, but unfortunately the values in
the rows below my insert
retain the same references, when I thought they would update by one
row.

I.e cell B3 did contain ='Investment M&G'!D3

I insert a new row and set (new) B3 to the same ='Investment M&G'!D3

but I was hoping B*4* would say ='Investment M&G'!D*4* , but it says
='Investment M&G'!D3

Any clever worksheet function syntax I can use to stop this? If it
helps the cells I
want to refer to in the other worksheet are always on the same row as
the worksheet I'm working on


--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=397940

dazman Wrote:
Hi,

I have a worksheet where using VBA I am inserting a new row and setting
cell values
in the new row to values specified in other worksheets. I can control
the values I want to refer
to in this new row with my VBA code, but unfortunately the values in
the rows below my insert
retain the same references, when I thought they would update by one
row.

I.e cell B3 did contain ='Investment M&G'!D3

I insert a new row and set (new) B3 to the same ='Investment M&G'!D3

but I was hoping B*4* would say ='Investment M&G'!D*4* , but it says
='Investment M&G'!D3

Any clever worksheet function syntax I can use to stop this? If it
helps the cells I
want to refer to in the other worksheet are always on the same row as
the worksheet I'm working on

Try using an INDIRECT worksheet function in combination with a ROW()
function as follows. If your formula is in A2 and by inserting a row
above with VBA you want it to refer to A3 use the following

=INDIRECT("Sheet1!A" & ROW())

The ROW with empty parentheses will return the same row as the cell
containing the formula which should work in your case


--
ronthedog
------------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php...o&userid=26504
View this thread: http://www.excelforum.com/showthread...hreadid=397940

Very useful tip for the future too


--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=397940