Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with application.Hlookup
Hi Brotherwarren,
you have set the searchRange so how about: colToFind = Application.HLookup(dateToday, searchRange, 2, False) Ken Johnson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |