This looked promising but TempVar did not pass into the formula and Excel
returned an error.
Mervyn

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