Vlookup still not working

I am working in Excel 97 and have the following code.

Private Sub CalcSchedule_Click()

Dim strCurrentSchedDate As Date
Dim strLookupDate As String
Dim strLookupYesNo As String

Sheets("NON WORKDAY").Select
Application.Rows("10:232").Select
Selection.Sort Key1:=Application.Range("A10"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Schedule").Select
strCurrentSchedDate = Cells(7, 3).Value
Range("A1").Value = strCurrentSchedDate
Cells(1, 2) = Application.VLookup(strCurrentSchedDate, _
Worksheets("NON WORKDAY").Range("A10:A500"), 2)

End Sub

The problem is that vlookup is returning #N/A. In fact, the date, 01/08/2005
I am looking up, is located in cell A12 on Worksheet "NON WORKDAY". Does
anybody know what could be the problem? I have confirmed, by stepping through
the code, that strCurrentSchedDate contains 01/08/2005.
--
M. Shipp

Try this:

Cells(1, 2) = Application.VLookup(clng(strCurrentSchedDate), _
Worksheets("NON WORKDAY").Range("A10:A500"), 2)

--
Regards,
Tom Ogilvy




"SHIPP" <[email protected]> wrote in message
news:[email protected]...
> I am working in Excel 97 and have the following code.
>
> Private Sub CalcSchedule_Click()
>
> Dim strCurrentSchedDate As Date
> Dim strLookupDate As String
> Dim strLookupYesNo As String
>
> Sheets("NON WORKDAY").Select
> Application.Rows("10:232").Select
> Selection.Sort Key1:=Application.Range("A10"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Sheets("Schedule").Select
> strCurrentSchedDate = Cells(7, 3).Value
> Range("A1").Value = strCurrentSchedDate
> Cells(1, 2) = Application.VLookup(strCurrentSchedDate, _
> Worksheets("NON WORKDAY").Range("A10:A500"), 2)
>
> End Sub
>
> The problem is that vlookup is returning #N/A. In fact, the date,
01/08/2005
> I am looking up, is located in cell A12 on Worksheet "NON WORKDAY". Does
> anybody know what could be the problem? I have confirmed, by stepping
through
> the code, that strCurrentSchedDate contains 01/08/2005.
> --
> M. Shipp

One other correction. You are trying to return data from column 2, but only
have a single column as the lookup range, so you need

Cells(1, 2) = Application.VLookup(clng(strCurrentSchedDate), _
Worksheets("NON WORKDAY").Range("A10:B500"), 2)




--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> Cells(1, 2) = Application.VLookup(clng(strCurrentSchedDate), _
> Worksheets("NON WORKDAY").Range("A10:A500"), 2)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "SHIPP" <[email protected]> wrote in message
> news:[email protected]...
> > I am working in Excel 97 and have the following code.
> >
> > Private Sub CalcSchedule_Click()
> >
> > Dim strCurrentSchedDate As Date
> > Dim strLookupDate As String
> > Dim strLookupYesNo As String
> >
> > Sheets("NON WORKDAY").Select
> > Application.Rows("10:232").Select
> > Selection.Sort Key1:=Application.Range("A10"), Order1:=xlAscending,
> > Header:=xlGuess, _
> > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > Sheets("Schedule").Select
> > strCurrentSchedDate = Cells(7, 3).Value
> > Range("A1").Value = strCurrentSchedDate
> > Cells(1, 2) = Application.VLookup(strCurrentSchedDate, _
> > Worksheets("NON WORKDAY").Range("A10:A500"), 2)
> >
> > End Sub
> >
> > The problem is that vlookup is returning #N/A. In fact, the date,
> 01/08/2005
> > I am looking up, is located in cell A12 on Worksheet "NON WORKDAY". Does
> > anybody know what could be the problem? I have confirmed, by stepping
> through
> > the code, that strCurrentSchedDate contains 01/08/2005.
> > --
> > M. Shipp
>
>