Mervyn

In your example tempvar and CodeLookup are treated
as characters in the string.

Instead try something like:

Sub Test()
'Leo Heuser, 21 Jan 2004
Dim CodeLookup As Range
Dim TempVar As Variant

With ActiveSheet
TempVar = .Cells(1, 1).Value
Set CodeLookup = .Range("B2:C4")
End With

ActiveCell.Formula = _
"=VLookup(" & TempVar & "," & CodeLookup.Address & ", 2, True)"

End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Mervyn Thomas" skrev i en meddelelse
...
I am trying to change a cell to a new value derived from looking itself up
in a list and cannot work out how to do it without using a temporary cell
to
hold the original data and then lookup using this. I cannot see why the
following does not work!

Dim tempvar
Cells(2, 5).Select
tempvar = Cells(2, 5)
ActiveCell.FormulaR1C1 = "=VLookup(tempvar, CodeLookup, 2, True)"
'CodeLookup is a list


Can anyone help please