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 finally finished it using the method you suggested. Here's the code:

Private Sub Method2()
    Dim tmpwksht As Worksheet
    Dim rngDates As Range
    Dim arrDates
    Set tmpwksht = Sheets.Add
    tmpwksht.Visible = False
    tmpwksht.Columns(1).Value = Sheet6.Columns(1).Value
    tmpwksht.Range("B2:B" & tmpwksht.Range("A2").End(xlDown).Row).Formula = "=INT(A2)"
    tmpwksht.Cells(1, 2).Value = "Header"
    tmpwksht.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tmpwksht.Columns(3), Unique:=True

    Set rngDates = tmpwksht.Range("C2", tmpwksht.Range("C" & Rows.Count).End(xlUp))
    arrDates = rngDates.Value
    arrDates = Application.Transpose(arrDates)
     
    ComboBox1.List = arrDates
    ComboBox2.List = arrDates
    
    Application.DisplayAlerts = False
    tmpwksht.Delete
End Sub

Just to note:

tmpwksht.Cells(1, 2).Value = "Header"

That is in there because otherwise AdvancedFilter didn't work unless I put in more code to specify the range.
Also, I used xlFilterCopy as that seemed faster than xlFilterInPlace. That took the AdvancedFilter so long that I finally hit ctl-break to stop it.

I also had another sub in there named Method1, that was the previous code I showed you. I then put in some simple time tracking code in the main sub that then called these two. Results from a test sheet with 30k entries:
The more duplicates there were, the closer the two ran together in time, to the point where the difference isn't detectable (> every other as dups).
The less duplicates there were (approaching to the point of complete uniqueness), the greater the difference. AdvancedFilter actually took 3 times longer to filter through everything than iterating through them row by row. I think this may be due to AdvancedFilter not expecting it's data to already be sorted.

 

[Norie]

One reason the filter is taking so long is because you are filtering an entire column.

You would only need that if you had a value in every cell on the column.

Also when checking for uniques it's going to check the entire range, and I've got a feeling checking one value against an entire column of values might take a bit.

I realise that most of the column will be blank, but you are telling VBA to include all those blank values.

Why not restrict the range to filter using something like this?

Range("B2:B" & tmpwksht.Range("A2").End(xlDown).Row)

You've already used it for the formulas, and the only thing you need to do is change it to include the header.


Here's the code with the changes I mentioned, and a few tweaks.

Private Sub UserForm_Initialize()
Dim tmpwksht As Worksheet
Dim rngOldDates As Range
Dim rngNewDates As Range
Dim arrDates
Dim LastRow As Long
 
    Set tmpwksht = Sheets.Add
 
    tmpwksht.Visible = False
 
    With Sheet2
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:A" & LastRow).Copy tmpwksht.Range("A1")
    End With

    Set rngOldDates = tmpwksht.Range("A1:A" & LastRow)
 
    tmpwksht.Range("B2:B" & LastRow).Formula = "=INT(A2)"
 
    tmpwksht.Range("B1").Value = "DatesWithoutTime"
 
    rngOldDates.Offset(, 1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tmpwksht.Range("D1"), Unique:=True

    LastRow = tmpwksht.Range("D" & Rows.Count).End(xlUp).Row

    Set rngNewDates = tmpwksht.Range("D2:D" & LastRow)
 
    arrDates = rngNewDates.Value

    arrDates = Application.Transpose(arrDates)
 
    ComboBox1.List = arrDates
    ComboBox2.List = arrDates
 
    Application.DisplayAlerts = False
    tmpwksht.Delete
    Application.DisplayAlerts = True
 
End Sub

Seems to work pretty fast, but I've only got a couple of thousand date/time values that goes down to about 300ish unique dates.

 

[FotS]

Still takes roughly the same amount of time. I don't think that AdvancedFilter is really sorting the blanks, because if you leave Row 1 blank and tell it to filter the column, it won't do it. No matter. I got a method that works and pretty quick... even if it does defy the laws of logic.

 

[Norie]

Advanced filter won't work without a header.

What method are you going to use then?

PS I've only be testing on a worksheet with just dates, nothing else on it.

Oh, I also have some code that eliminates the need for any temporary worksheets or advanced filters.

It works via ADO by running some simple SQL queries against the data.

Just tested with 20000 date/times and seems quite quick.