Problem with application.Hlookup

[Brotherwarren]

Hi all,
I'm experiencing a problem with a userform that I am trying to compose
at the moment
I am trying to find a value in a worksheet's cell by using
application.worksheetsfunction.hlookup
or
application.Hlookup (have tried both but get same results each time)
I get an error 2042 or 2015, which error( ) shows as
"application-defined or object-defined error"?

The code is contained in a userform's commandButton_click event
handler. The userform itself loads from another userform's
commandButton. Both the Parent and daughter userforms are set to be
non-modal.

Here's the code

Sub btnSetHW_Click()
Dim DueDate As Date
Dim dateToday As Date
Dim colToFind As Variant
Dim searchRange As Range
DueDate = CalDueDate.Value
dateToday = Date
Select Case dateToday
Case 38768 To 38809
Set searchRange = Worksheets("planner").Range("c4:ar73")
colToFind = Application.HLookup(dateToday, "c4:ar73", 2, False)

Debug.Print colToFind

Case 38824 To 38865

Case 38897 To 38938
End Select


End Sub


Any suggestions? (Offers of everlasting sevitude in advance)

 

[Ken Johnson]

Hi Brotherwarren,
you have set the searchRange so how about:
colToFind = Application.HLookup(dateToday, searchRange, 2, False)
Ken Johnson

 

[Brotherwarren]

hi Ken, thanks, but no joy I'm afraid. The set command is there from
an earlier attempt at this format. Still produces the 2042 error
unfortunately.

 

[Brotherwarren]

Problem solved. Sorry to all who spent their time trying to help. I
was being a dimwit. Made the mistake of using two similar sounding
names for variables and was trying to lookup a value that did not
exist. Thanks again Ken.

 

[Brotherwarren]

Problem solved. Sorry to all who spent their time trying to help. I
was being a dimwit. Made the mistake of using two similar sounding
names for variables and was trying to lookup a value that did not
exist. Thanks again Ken.

 

[Brotherwarren]

hi all, prolem solved. I was being a dimwit. Had used two similar
sounding variables and I was trying to lookup the wrong one. The 2042
error resulted because the value to be lookup'd wasn't in the range I
was lookup'ing. Thanks to all those who helped.

 

[Ken Johnson]

Hi Brotherwarren,
Thanks for the feedback. Your problem really had me puzzled. Don't be
too hard on yourself though!
Ken Johnson