vlookup function in vba

[R.Venkataraman]

I want to use a vlookup functin in vba

Range("a15") = "=vlookup(d12,a1:b6,2,false)"
this works ok.

But can I use a VARIABLE in lookup_value (i.e. instead of D12)
I am giving a sample code more to clarify the point to myself

code begins
Dim datarange As Range
Dim myrange As Range
Set datarange = Range("a1:B6")
'(a few of the cells from the col A of datarange is "myrange")
Set myrange = Range("d12:d14")
For Each c In myrange
c.Offset(0, 1) = "=vlookup(c,datarange,2,false)"
Next
code ends
this does not give the desired result . c.offset(0,1) values become
0(zero). where is the error in my logic. is it something to do with the
vlookup function being a worksheet function and not a vba function. I even
used
c.address or even c.value instead of c with no success(the results are
#name?). does it mean I
cannot use a variable in vlookup function.
Of course I have designed the code in some other way without using the
variable for lookup_value to get the desired result.

 

[JohnI in Brisbane]

R.Venkataraman,

If you want the addresses, the following works-

c.Offset(0, 1) = "=vlookup(" & c.Address & "," & datarange.Address &
",2,false)"

If you want the value of "c", the following work (a) Numeric values (b)
Alphanumeric or text values -

a) c.Offset(0, 1) = "=vlookup(" & c.Value & "," & datarange.Address &
",2,false)"

b) c.Offset(0, 1) = "=vlookup(""" & c.Value & """," & datarange.Address &
",2,false)"

regards,

JohnI

 

[Don Guillett]

This will leave the value of the formula at the offset. No need to clutter
up with formulas.

For Each c In Range("d12:d14")
c.Offset(, 1) = Application.VLookup(c, Range("a1:b6"), 2, 0)
Next

 

[R.Venkataraman]

thanks both of you.

This will leave the value of the formula at the offset. No need to clutter
up with formulas.

For Each c In Range("d12:d14")
c.Offset(, 1) = Application.VLookup(c, Range("a1:b6"), 2, 0)
Next