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.
--
Ken Hudson


"ricm9" wrote:

>
> 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?
>
>
> --
> ricm9
> ------------------------------------------------------------------------
> ricm9's Profile: http://www.excelforum.com/member.php...o&userid=34613
> View this thread: http://www.excelforum.com/showthread...hreadid=543865
>
>

[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]...
>
> 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?
>
>
> --
> ricm9
> ------------------------------------------------------------------------
> ricm9's Profile:
> http://www.excelforum.com/member.php...o&userid=34613
> View this thread:
> http://www.excelforum.com/showthread...hreadid=543865
>

[ricm9]

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

I have adjusted the values such that there will always be a match, but 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 date matches in column 'A', and then return a corresponding value in column '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]...
>
> Now I get error 2042. What does this error mean?
>
> I have adjusted the values such that there will always be a
> match, but
> 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
> date
> matches in column 'A', and then return a corresponding value in
> column
> 'C'.
>
> What a I doing wrong?
>
>
> --
> ricm9
> ------------------------------------------------------------------------
> ricm9's Profile:
> http://www.excelforum.com/member.php...o&userid=34613
> View this thread:
> http://www.excelforum.com/showthread...hreadid=543865
>