Runtime error '1004'

[Dkso]

Error message given is "Unable to get the Vlookup property of the
WorksheetFunction class"

I have a Combobox that selects an item in a list, this item is selected then
I receive the error message.

Help please.

Thanks
Dean

 

[Tom Ogilvy]

Entries in a combobox are strings, so it sounds like Vlookup can't find the
match (a string won't match a date)

Dim results as Variant
results = Application.Vlookup(cDate(combobox1.Value),
Range("Sheet5!A1:F365"),4,False)
if iserror(results) then
msgbox combobox1.Value & " was not found"
else
msgbox results " is the result"
End if

 

[Dean]

Can I do a search for a date, I eventually want to create a diary, list
of dates where I can find the date and put entries into the cell next
to it.

 

[Tom Ogilvy]

Sure, but if that is what you are doing, it might be easier to use an
autofilter under the data menu. Anyway, one way would be:

Private Sub Combobox1_click()
Dim results as Variant
Dim rng as Range
' diary range with dates
Set rng = worksheets("Sheet1").Range("A1:A2000")
results = Application.Match(clng(cDate(combobox1.Value)), _
rng,0)
if iserror(results) then
msgbox combobox1.Value & " was not found"
else
Worksheets("Sheet1").Select
rng(results).Select
End if
End Sub