What's wrong with this bit of code

M

Mervyn Thomas

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
 
G

George Nicholson

Specify 'Range("CodeLookup")' rather than just 'CodeLookup'? There may be
something else going on, but that's my initial knee-jerk reaction.
 
P

pauluk

I know this may sound silly but have you tried using labels to define
the cells? This is one thing i always forget to do
 
L

Leo Heuser

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
 
M

Mervyn Thomas

Afraid that didn't work plus a few alternatives. The lookup part of the
equation works OK anyway - what is wrong is passing the variable into the
eqation and what gets put in the cell is =Vlookup(tempvar,codelookup.......)
and what I need is the value of tempvar in the equation.
Thanks for the suggestion
Mervyn
 
M

Mervyn Thomas

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

Leo Heuser

Works OK in Excel 2002 SP-2.

Was it an Excel (error in cell) or an VBA error?
What error was returned?

How does your final code look?

Have you tested my example exactly as it
is written?

LeoH
 
G

George Nicholson

Try this:

Dim tempvar

tempvar = Cells(2, 5)
Cells(2, 5) = "=VLookup(" & tempvar & ", CodeLookup, 2, True)"
 
Top