| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What is wrong with this code? | Excel Discussion (Misc queries) | |||
| What is wrong with the code? | Excel Discussion (Misc queries) | |||
| Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
| What's wrong with my code ? | Excel Worksheet Functions | |||
| Is something wrong with the code | Excel Programming | |||
Linear Mode
