What's wrong with this bit of code
 

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

What's wrong with this bit of code
 

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

--
George Nicholson

Remove 'Junk' from return address.


"Mervyn Thomas" wrote in message
...
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

What's wrong with this bit of code
 

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 Thomas wrote:
*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 *


---
Message posted from http://www.ExcelForum.com/

What's wrong with this bit of code
 

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

What's wrong with this bit of code
 

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

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

--
George Nicholson

Remove 'Junk' from return address.


"Mervyn Thomas" wrote in message
...
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

What's wrong with this bit of code
 

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

What's wrong with this bit of code
 

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


"Mervyn Thomas" skrev i en meddelelse
...
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

What's wrong with this bit of code
 

Try this:

Dim tempvar

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

--
George Nicholson

Remove 'Junk' from return address.


"Mervyn Thomas" wrote in message
...
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

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

--
George Nicholson

Remove 'Junk' from return address.


"Mervyn Thomas" wrote in message
...
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