Vlookup in Procedure...I'm begging you to help...
Posted by Tim on February 07, 2002 6:29 AM
Here is my code
Sub CopySegments()
Dim R As Integer
R = Sheets("Total").Range("b65536").End(xlUp).Row
For i = 2 To R
Sheets("Total").Cells(i, 13).Value = _
Application.WorksheetFunction.VLookup(Sheets("Total").Cells(i, 2).Value, _
Sheets("MDMS Data").Range("c2:p100"), 2, 1)
Next i
End Sub
It bombs because it says it can't find the Vlookup something. The thing is, when I set the range in the Vlookup function to Sheets("MDMS Data").UsedRange, it works just fine. I don't want UsedRange though, I want a specific range. What's the problem?
Posted by John Beaudoin on February 07, 2002 6:40 AM
The problem is that the VLOOKUP command will not work with a variable in the table_name input. You must have the actual name of the range. I belive the function is looking for the name of a range "Cells(i,2)" and you problably do not have a range named that.
I am running into the same problem right now. Does anyone know how to sneak a reference to the value in another cell or variable into a VLOOKUP command? If not can anyone design another fuction that will act like VLOOKUP by will take a variable?
Posted by John Beaudoin on February 07, 2002 6:56 AM
I was given the following help and it may work for you:
Use the function INDIRECT(VLOOKUP(...))
Posted by John Beaudoin on February 07, 2002 7:03 AM
I messed up earlier. This is the formula:
VLOOKUP(itemtolookup,INDIRECT(variable),row)
