MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)