ExcelBanter - VLookup

Sub Tester3()
Dim ProcCovered As String * 1
Dim PopGroupCol As Integer
Dim MyRange As Range
CurrRow = 4
Set MyRange = Worksheets(2).Range("A1:AD173")
PopGroupCol = 16
ProcCovered = Application.WorksheetFunction. _
VLookup(Worksheets(1).Cells(CurrRow, 4).Value, _
MyRange, PopGroupCol, False)
MsgBox ProcCovered
End Sub

worked fine for me when I defined what the value of CurrRow is.

However, you will get an error such as this if the value is not found. You
have to set up your code to handle this error.

Sub Tester3()
Dim ProcCovered As String * 1
Dim PopGroupCol As Integer
Dim MyRange As Range
CurrRow = 4
Set MyRange = Worksheets(2).Range("A1:AD173")
PopGroupCol = 16
On Error Resume Next
ProcCovered = Application.WorksheetFunction. _
VLookup(Worksheets(1).Cells(CurrRow, 4).Value, _
MyRange, PopGroupCol, False)
If Err = 0 Then
MsgBox ProcCovered
Else
MsgBox "Not found"
End If
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy

"CherylM" wrote in message
...
I'm having trouble making the function work. I have coded the
following...

Dim ProcCovered As String * 1
Dim PopGroupCol As Integer
Dim MyRange As Range

Set MyRange = Worksheets(2).Range("A1:AD173")
PopGroupCol = 16
ProcCovered =
Application.WorksheetFunction.VLookup(Worksheets(1 ).Cells(CurrRow,
4).Value, MyRange, PopGroupCol, False)

I know that the value for which I am searching is in the range, so I
would expect to get a value in ProcCovered. However, instead, I'm
getting the following error: "1004 - Unable to get the Vlookup property
of the WorksheetFunction class".

OR...does anyone know another way that I can accomplish what I'm trying
to do...using a value from one spreadsheet to look in the first column
of another spreadsheet to find a match and then return a value from the
same row, but in a different column.

Help?!?!
Thanks!