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.

 

[Norie]

The LookAt argument of Find specifies whether to look for an exact (xlWhole) or partial (xlPart) match.

As for the comboboxes, how many entries are you talking about and why couldn't you list them in a combobox?

The user wouldn't need to scroll through every item on the list to find the right one, they could just type it.

Anyway, if it's working with the dat picker.

 

[FotS]

Oh, right, ok.

As for the number of entries... I've just recently started the Log and we're already at >70 entries. This is intended as a long term log, so I'm designing it with that in mind.

Either way, though, even if I do use an alternate solution, I'd still like to get to the bottom of why the error is occurring in case I ever run into a situation where there isn't any other practical solution.

 

[FotS]

bump from pg 14

 

[FotS]

So, just to add a bit more info, if I take the code from the Form and put it to the Sheet's code, it comes out with an "Application-defined or object-defined error."

And before hitting hitting post... I tried a couple more variations. Here's the one that worked....

MsgBox Application.WorksheetFunction.VLookup(Range("A5"), Columns(1), 1)

So it looks like it's looking for a Range object as Arg1 instead of an actual value. How lame....

 

[Norie]

Its not expecting a range.

You can use a value there just as you can on a worksheet.

When you tested the formula on a worksheet what exactly did you try?

Just like on a worksheet the data type of the lookup and the range you are looking up need to match.

Since you are looking for dates/times and the values in a combobox are actually text you need to convert them to 'real' date/times.

This is another reason why using another approach might be an idea.

Final thing, I just noticed you have no worksheet reference for Columns(1).

That means that VBA will look at Columns(1) on whatever sheet it regards as active.

 

[FotS]

Correct on the lack of Sheet reference, and that's because that code I posted is on the sheet itself in question. When on the Form, I used Sheet2.

As for the value it's expecting:

Application.WorksheetFunction.VLookup(DateValue("6/29/2011"), Sheet2.Range("A:A"), 1, True)

Was not working. Sheet side, replacing

DateValue("6/29/2011")

with

Range("A5").Value

also was not working. It didn't work until after I took out the .Value

 

[Norie]

Well I can't think of anything else really.

Using dates is always tricky when doing this sort of thng.

That's why I prefer something like a combobox/listbox, it kind of eliminates the problem because you don't need to do any sort of searching.

 

[FotS]

I don't know. Kind of sucks really.

Oh, and the formula that I used on the sheet (non-VBA) that worked was this:

=VLOOKUP(DATEVALUE("6/29/2011"),A:A,1)

 

[Norie]

It's annoying but it can be done.

Have you tried Find at all?

 

[FotS]

Not yet. Been busy with other work projects atm.

What I think I'm going to end up doing is switching to the ComboBoxes like you suggested and then use Find to grab the selected cells. Hopefully comboboxes can't be easily overloaded.... I don't know what their item limit is. Although, if it's 65536 or better, I guess it doesn't really matter! lol

On the other hand, probably what I can do is create some kind of "archive" after so many entries or time has gone by. Last month we added in 30 entries, while this month we're already up to 25. Even assuming 50 entries a month, that's only 600 entries a year. If the combobox can at least handle that, I think it might be ok. Although I have a concern of "lag" as the code has to loop through all 600 rows on the sheet to add all the dates to the comboboxes....