What's wrong with this bit of code

  • Thread starter Thread starter Mervyn Thomas
  • Start date Start date
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
 
Specify 'Range("CodeLookup")' rather than just 'CodeLookup'? There may be
something else going on, but that's my initial knee-jerk reaction.
 
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
 
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
 
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
 
This looked promising but TempVar did not pass into the formula and Excel
returned an error.
Mervyn
 
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
 
Try this:

Dim tempvar

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