Formulas change when macro adds new column
 

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?

Formulas change when macro adds new column
 

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"

--
Regards,

OssieMac


"Sandra" wrote:

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?

Formulas change when macro adds new column
 

I will try that. Thank you.

"OssieMac" wrote:

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"

--
Regards,

OssieMac


"Sandra" wrote:

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?

Formulas change when macro adds new column
 

On Jan 24, 7:44*am, Sandra wrote:
*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?

INDIRECT works in VLOOKUP for me, eg...

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

Ken Johnson