Formulas change when macro adds new column

  • Thread starter Thread starter Sandra
  • Start date Start date
S

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:D. 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?
 
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"
 
I will try that. Thank you.

OssieMac said:
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"
Click to expand...
 
 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:D.  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?
Click to expand...

INDIRECT works in VLOOKUP for me, eg...

=VLOOKUP(A1,INDIRECT("C1:D6"),2,0)

Ken Johnson
 
Back
Top