Vlookup's in macros

[Guest]

Im trying to create a macro that has a vlookup in it. When I copy the
vlookup to the cell below itself (copy from b2 to b3), the vlookup still
references the previous cell (example a2,c2:h4,1,false, where the a2 does not
automatically change to a3 when copied to b3).

 

[Roger Govier]

Hi

How did you do the copy?
If you highlit the formula in the formula bar, did Copy, Esc and then
Pasted to A3 the formula would not cage.
If you copied cell A2 and Pasted to cell A3 it will, or if you drag with
the fill handle.

You don't show any $ signs in your formula, so I am presuming that you
don't have absolute references set like
=VLOOKUP($A$2,C2:H4,1,false)

Also, as your offset is 1, it should be returning the same value as
exists in A2, provided that exists within the range C2:C4.
Is that what you wanted?

 

[Guest]

Roger,
I double clicked the handle at the bottom right of the cell to automatically
fill to the bottom of the entire data set. Is there a way that I can go into
visual basic to rewright the code to make it say "always reference one cell
to the left?" For example, instead of putting vlookup(a2,$c$2:$h$4,2,false),
could I put something along the lines of vlookup(1 cell to the
left,$c$2:$h$4,2,false)?

 

[Roger Govier]

Hi

I think you need to explain with examples of what your data consists of,
what it is that you are trying to do.
It might be that you should be using something other than Vlookup.
It you can show some examples of what the data is in cells C2:H4 and
what is in A2 and what you want to achieve, then I'm sure we should be
able to help you.