Formulas change when macro adds new column

[Sandra]

Spreadsheet has formulas that pick up values from data tab. Macro inserts a
new column B in the data tab. When it does that, the formulas move from B:C
to C. The formula is in a vlookup. How do I get the formula to stay as
B:C. I tried INDIRECT but it does not work in VLOOKUP. Any ideas?

 

[OssieMac]

Hi Sandra,

As you are using a macro to insert the column you can initially name the
range B:C on the worksheet (See 'define name' in the worksheet help) and then
use the named range in the formula like the following (I have named the range
'MyLookUpRng':-

=VLOOKUP(B1,MyLookUpRng,2,FALSE)

Then in the macro after inserting the column rename columns B:C to the named
range like the following (Note C2:C3 is columns 2 and 3):-

Sheets("Data").Columns("B:B").Insert Shift:=xlToRight

ActiveWorkbook.Names.Add Name:="MyLookUpRng", _
RefersToR1C1:="=Data!C2:C3"

 

[Sandra]

I will try that. Thank you.

Hi Sandra,

As you are using a macro to insert the column you can initially name the
range B:C on the worksheet (See 'define name' in the worksheet help) and then
use the named range in the formula like the following (I have named the range
'MyLookUpRng':-

=VLOOKUP(B1,MyLookUpRng,2,FALSE)

Then in the macro after inserting the column rename columns B:C to the named
range like the following (Note C2:C3 is columns 2 and 3):-

Sheets("Data").Columns("B:B").Insert Shift:=xlToRight

ActiveWorkbook.Names.Add Name:="MyLookUpRng", _
RefersToR1C1:="=Data!C2:C3"

 

[Ken Johnson]

 Spreadsheet has formulas that pick up values from data tab.  Macro inserts a
new column B in the data tab.  When it does that, the formulas move from B:C
to C.  The formula is in a vlookup.  How do I get the formula to stay as
B:C.  I tried INDIRECT but it does not work in VLOOKUP.  Any ideas?

INDIRECT works in VLOOKUP for me, eg...

=VLOOKUP(A1,INDIRECT("C16"),2,0)

Ken Johnson