Using a range from different worksheet in VLOOKUP in VBA
 

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

Using a range from different worksheet in VLOOKUP in VBA
 

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

--
Don Guillett
SalesAid Software

"Janice" wrote in message
ups.com...
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