Using a varialbe in a cell reference

[trodneyrotter]

Hi,

I'm trying to use a variable as a cell reference in a formula using '&', but can't seem to get it to work. I've looked through the forum, which have examples of how to do this using a range, but not just as a single cell reference. This puts the formula '=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)", which obviously gives me an error.

My code is:

Dim lastrow As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1

    ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 1).Select
    ActiveCell.Formula = "=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)"

Grateful if someone could point out what I'm doing wrong.

Thanks

 

[vds1]

What value does Vlookupinput Variable hold during run time ?

 

[Darren Bartrup]

Have you tried:

"=VLOOKUP(A" & lastrow & ",Vlookupinput,2,0)"

 

[trodneyrotter]

Hi,

Thanks for for the quick respone. VLookupInput is the named range that the formula will use. E.g. if it worked it would enter the formula '=VLOOKUP($A30, Vlookupinput, 2,0)' A30 being the cell that contains numbers 1 to infinity.

Thanks.

 

[Norie]

The only thing wrong with the code is the "", it should just be ".

 

[trodneyrotter]

Have you tried:

"=VLOOKUP(A" & lastrow & ",Vlookupinput,2,0)"

Thank you, that's worked nicely.