Using a range from different worksheet in VLOOKUP in VBA

  • Thread starter Thread starter Janice
  • Start date Start date
J

Janice

I have been having trouble trying to put a range into the VLookup
function. My range/table is in another worksheet, called Critical
Values, in the same workbook. How could I reference a range from a
different workbook or a different worksheet? I would perfer to
reference a different workbook if possible. Here is what I have:

**CN is a range I already defined inside the worksheet itself.
Do I need to define it again in the code? And if so, how?

Sub()

Dim Countdata As Double
Dim V As Double
Set CN As Range

Countdata = WorksheetFunction.CountA(ActiveSheet.Range(Cells(1,
ColIndex), Cells(Row, ColIndex)))

V = Worksheetfuncion.VLookup(Countdata,
Worksheet("CriticalValues").Range("CN"), 2, True)

End Sub
 
your sub has other problems but this works from anywhere in the workbook
where lur is your named range

Sub lu()
MsgBox Application.VLookup(3, [lur], 2)
End Sub
 
Back
Top