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