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.

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

"R.Venkataraman" wrote in message
...
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.

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" wrote in message
...
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.

thanks both of you.

"Don Guillett" wrote in message
...
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" wrote in message
...
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.