Using VLookup formula in a macro

[Relative Ref in VLookup for search value]

I am creating a visual basic macro to work with time data. In the data the
staff names are in the first column. I want a macro that can be started at
multiple locations in the worksheet. I want to pull data from a VLookup
table the macro has inserted on another part of the worksheet.

My macro is using relative reference with a multiply formula and works fine.
When I try to use a specific cell, say A3 as the search value for the
VLookup arguments, it works fine, but only grabs the absolute cell A3. So I
tried to use RC relative reference for the search value and It says compile
error. I tried every imaginable way to fill in the search value cell and I
can't get it to work.
A Line of macro follows:
ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"
What form of reference might work?

 

[Conan Kelly]

Relative Ref in VLookup for search value,

I don't have too much experience with stuff like this, but you might try the
"FormulaR1C1" property of the range object:

ActiveCell.Offset(0, 16).FormulaR1C1 = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"

instead of

ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"

Another thing you could try is something like this:

ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(" & cells(activecell.row,
activecell.column - 16).address & ",V$3:W$20,2,FALSE)"

Look up the "Address" property of the range object in help. It has some
arguments that can effect the style (relative, absolute, external) of the
address returned.

Something else I noticed, your reference to the lookup table has absolute
rows, but the columns are relative. If you ever have to copy/fill this
formula accross, your look up table will change. You might consider making
the lookup tables columns absolute as well if that is a concern.

HTH,

Conan







"Relative Ref in VLookup for search value" <Relative Ref in VLookup for
search (e-mail address removed)> wrote in message
news:[email protected]...

 

[Relative Ref in VLookup for search value]

Relative Ref in VLookup for search value,

I don't have too much experience with stuff like this, but you might try the
"FormulaR1C1" property of the range object:

ActiveCell.Offset(0, 16).FormulaR1C1 = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"

instead of

ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"

Another thing you could try is something like this:

ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(" & cells(activecell.row,
activecell.column - 16).address & ",V$3:W$20,2,FALSE)"

Look up the "Address" property of the range object in help. It has some
arguments that can effect the style (relative, absolute, external) of the
address returned.

Something else I noticed, your reference to the lookup table has absolute
rows, but the columns are relative. If you ever have to copy/fill this
formula accross, your look up table will change. You might consider making
the lookup tables columns absolute as well if that is a concern.

HTH,

Conan







"Relative Ref in VLookup for search value" <Relative Ref in VLookup for
search (e-mail address removed)> wrote in message

I am creating a visual basic macro to work with time data. In the data the
staff names are in the first column. I want a macro that can be started
at
multiple locations in the worksheet. I want to pull data from a VLookup
table the macro has inserted on another part of the worksheet.

My macro is using relative reference with a multiply formula and works
fine.
When I try to use a specific cell, say A3 as the search value for the
VLookup arguments, it works fine, but only grabs the absolute cell A3. So
I
tried to use RC relative reference for the search value and It says
compile
error. I tried every imaginable way to fill in the search value cell and
I
can't get it to work.
A Line of macro follows:
ActiveCell.Offset(0, 16).Formula = "=VLOOKUP(RC[-16],V$3:W$20,2,FALSE)"
What form of reference might work?