Maintain formula references while inserting co

mr. white · Sep 18th 2003

I have a question. When i input data into sheets using VBA, it also inserts columns to add some data. When it does this, it changes the reference I have on another sheet to values in that sheet. Is there a way that i can keep the reference in the one sheet from moving when I insert columns on the sheet that it is referencing, besides using $$$. It still moves the reference.

To clarify, a formula in the "raw" sheet references column groups $D:$G in the "R1" sheet. When VBA finishes inserting the columns into the R sheets, the reference in the formula changes to $F:$I. Is there a way to maintain the correct reference in the formula on the "raw" sheet while inserting columns in the "R1" sheet. Thanks for the help!!!

I hope this makes sense...

james_skeggs · Sep 18th 2003

Why dont you try inserting the formula after into the cells that you want after you have inserted the columns within the macro:

Code

Sub Insert()


dim rngTarget as string


Insert columns
with sheets("Sheet1")
set rngTarget = e.g. Range(.cells("A1"), .cells("A65536")..End(xlUp).Offset(1, 0))
rngTarget.formulaR1C1 = "=your formula here"


End Sub

Display More

Barrie Davidson · Sep 18th 2003

Could you use the indirect function? Something like:

=SUM(INDIRECT("R1!D1:G1"))

Regards,

Maintain formula references while inserting co

Participate now!

Share