Problem with application.Hlookup
 

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)

Problem with application.Hlookup
 

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

Problem with application.Hlookup
 

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.

Problem with application.Hlookup
 

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.

Problem with application.Hlookup
 

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.

Problem with application.Hlookup
 

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.

Problem with application.Hlookup
 

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