VBA Vlookup error

[FotS]

Ok, so here's the code:

Private Sub SelectSheet2DateRange()
    MsgBox (ThisWorkbook.Application.WorksheetFunction.VLookup(DTPicker1.Value, "A:A", 1))
End Sub

And here's the error:

-- removed inline image ---


Anybody know why? The code is being executed from a form.

 

[wigi]

Try:

Private Sub SelectSheet2DateRange()
    MsgBox Application.VLookup(DTPicker1.Value, Columns(1), 1)
End Sub

 

[FotS]

Thanks for the reply and suggestion.

Removed outer () and changed "A:A" to Sheet2.Columns(1). Same error.

 

[FotS]

Sorry, just now seeing that the error pic didn't post.

Run time error '1004'.
Unable to get the VLookup property of the WorksheetFunction class.

 

[wigi]

Does it work when you do the vlookup() in the sheet itself (so without VBA but with the same arguments to the function)?

 

[FotS]

Hmm, nope. #VALUE error.

Ok, so here's the setup:
Column A (Aside from Row 1 as this is the Header), is a log of date/times formatted so they come out looking like so: "7/18/2011 10:30:29 AM"
The form is a printing form that asks the user the date range they want printed from the log. It calls a sub that selects the specified date range so that then it can print out the selection only. I know that Find is going to look for an exact match and the specified date range isn't necessarily going to have exact matches, hence my using Vlookup.

 

[wigi]

Then first thing to do is make it work as an ordinary VLOOKUP formula, without VBA. After that, we can try to do the same thing in VBA.

 

[Norie]

You can set Find to look for partial matches.

More importantly you need to specify the column for a VLookup (vertical lookup) and can't look across columns.

Perhaps you could try HLookUp or Find, set to look for partial matches.

Or why not create 2 comboboxes that list all the headings?

Then the user can pick from one for the start and the other from the end.

You could use their ListIndex property to determine the columns, so you wouldn't need Find or a formula.

 

[Norie]

Oops, my terrible.

Just re-read your last post.:

Ignore all the stuff about searching across columns.

The Find and combobox suggestions still stand though.

 

[FotS]

Thanks for the replies.

@wigi: My bad, VLOOKUP on the sheet is working... I was accidentally transposing the "" with the cell reference from my first attempt. Taking those out it works fine.

@Norie: How can Find be used to find partials? I've looked through all of its arguments and I didn't see any flags for that. Either way, if someone selects a date that isn't in the log, then I need it to select the next closest date. Also, a combo box may not work too well. The potential number of entries into the log is... well... growing large. :P The date picker seemed the easiest solution.