VLookup error

[ricm9]

I get error 1004; "Unable to get the VLookup property of the
WorksheetFunction class" on the following line of code:

-myHoliday = Application.WorksheetFunction.VLookup(myDate,
Worksheets("Supporting Sheet").Range("PayPeriod"), 3)-

I'm running Excel 2002. Any suggestions?

 

[Ken Hudson]

Hi,
When I have used VLOOKUP in VB, I use the following:

Range("A1") = "=VLookup(MyDate, 'Supporting Sheet'!PayPeriod, 3)"

You might try that code.

 

[Chip Pearson]

You'll get that error if VLOOKUP doesn't find a match. Instead,
use

Dim myHoldiday As Variant
myHoliday = Application.VLookup(myDate, _
Worksheets("Supporting Sheet").Range("PayPeriod"), 3)-
If IsError(myHoliday) = True Then
' not found
End IF


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"ricm9" <[email protected]>
wrote in message
news:[email protected]...

 

[ricm9]

Now I get error 2042. What does this error mean?

I have adjusted the values such that there will always be a match, bu
that doesn't seem to help.

Here is the action I want:
Given a supplied date value, I am trying to find the row who's dat
matches in column 'A', and then return a corresponding value in colum
'C'.

What a I doing wrong

 

[Chip Pearson]

A 2042 error is the same as #N/A error. It means VLOOKUP didn't
find what it was looking for. You might have numbers formatted as
text or special characters in the cell.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ricm9" <[email protected]>
wrote in message
news:[email protected]...