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.

 

[FotS]

Ok, so after some testing, I don't notice any real lag in adding items to the comboboxes until around 5000 entries or so (which assuming 50 a month = 600 a year, that's 8.33 years) and while 65535 overflows the combobox, 15000+ does not (25 years).

Don't think I have to worry about it! lol

Before posting, I gave finding the limit on the comboboxes a quick go. Starting in Row 2, it overflows on row 32768, which means 32766 items can be added to a combobox. That's 54.61 years. lol

 

[Norie]

How are you adding the items?

 

[FotS]

Sorry for the late reply. Was busy for the remainder of the day.

Here's the code:

    Dim i As Integer 'i used in For loop
    Dim curCellDate As Date 'curCellDate used in For loop to cut down on cell references
    For i = 2 To Sheet2.Range("A2").End(xlDown).Row
        curCellDate = Application.WorksheetFunction.Text(Sheet2.Cells(i, 1).Value, "mm/dd/yyyy")
        ComboBox1.AddItem curCellDate
        ComboBox2.AddItem curCellDate
    Next i

Still need to develop a self check so that duplicates don't get in (multiple entries on the same day).

Speaking of which... is there any way to reference a specific value in a combobox (preferably the last one) without having to define a second variable to keep track of it for me?

 

[Norie]

There are probably other ways to populate your comboboxes, and there's definitely something you can replace the call the the worksheet function Text - nothing.

Dim rngDates As Range
Dim arrDates
 
Set rngDates = Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
 
arrDates = rngDates.Value
 
arrDates = Application.Transpose(arrDates)
 
Combobox1.List = arrDates
 
Combobox2.List = arrDates

This should be quicker, and hopefully should retain the format of the values.

Actually I thought it was date and time not just date?

Wasn't that why you were using a DateTime Picker?

One more thing, you could try just populating the first combobox and only populate the second when a selection is made in the first.

This would allow you to, in the second combobox, only list the dates after the date selected in the first listbox.

Now the duplicates, I can think of a way to deal with that, but it really depends on whether or not it is date or date and time.

 

[FotS]

The log is setup with date and time in the cells, but the printing form is going to go by date only, hence the Text call to reformat the value. I've actually been unsuccessful in getting the DTPicker to display both in one box on another form, but that's a moot point.

Just to note, too, the log is arranged with header row in row 1, newest entry on row 2, and oldest entry on the bottom.

 

[Norie]

If you do need to reformat the value, use VBA's Format not the worksheet function Text.

Using the worksheet function might actually be slowing things down.

If you just want dates displayed in the combobox and you also just want duplicates then it could be possible to do both at the same time.

It might involve a temporary worksheet though.

 

[FotS]

You're right, the method you gave is a heck of a lot faster. On a test sheet with 30000 dates on it, your method takes barely a second to load, vers mine taking 3-5 secs, depending on if I populate 1 combobox or both.

But yeah, I do want to format them. I had forgotten about the format functions available through VBA, hence my using Text.

Via your method though, I'm not seeing an easy way to reformat and check for dups? It looks like I'd have to iterate the array to do this (unless you know of a shortcut function), which brings us back to being similar to my method.

So, I guess with that in mind, here's my final code to populate the combobox. As I said, un-commenting the ComboBox2 line adds 2 secs to the load time for 30k entries.

    Dim i As Integer 'i used in For loop
    Dim curCellDate, prevCellDate As Date 'used in For loop to cut down on cell references
    For i = 2 To Sheet2.Range("A2").End(xlDown).Row
        curCellDate = FormatDateTime(Sheet2.Cells(i, 1).Value, vbShortDate)
        If curCellDate <> prevCellDate Then
            ComboBox1.AddItem curCellDate
            'ComboBox2.AddItem curCellDate
            prevCellDate = curCellDate
        End If
    Next i

Edit: I think though I'll leave that line enabled so both get populated together. I'd like to have it set up so if the user just wants to print the whole log they don't have to waste time selecting the dates from the comboboxes first.

 

[Norie]

Did you not see my last comment about formatting, duplicate data and a temporary worksheet?

As for the formatting thing, if you format the cells on the worksheet to only show date then you shouldn't need to format in code.

That would only work if you did loop and used AddItem but you could just format the data temporarily while you are populating the combobox.

In fact that brings me back to the idea I had to eliminate duplicates etc.

First you would copy all the values to a temporary worksheet.

Next strip the time from them.

Then run advanced filter to produce a unique list of dates.

Use that list to populate the comboboxes.

 

[FotS]

Ok, so giving it a try. So far, AdvancedFilter seems to take longer (almost double) than the method I use. But, a couple small issues:

1) How do I actually strip the time out of the values in a range without iterating through it? All of the formatting functions seem to expect a single value ("Type mismatch" error) and .NumberFormat = "mm/dd/yyyy" only changes the appearance, not the actual value.

2) How do I remove a temp worksheet (assuming I stored it to var "tmpwksht") without the "Are you sure you want to delete this" prompt coming up?

 

[Norie]

1 A simple formula like =INT(A2) will strip out the time, and you run the advanced filter on the data without time.

2 Just turn off display alerts.

I've actually got an example of 1 which I can post but it's a bit rough and ready and is probabl best tried in a new, blank workbook.

Hopefully it'll demonstrate the idea.

I can post the code, or if you give me some time upload an example workbook and post a link.