Having trouble with how to format search criteria to match dates in a column- vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
My dates are found in column B and in the format “dd-mm-yy” – this is how they look to my eyes but when I click on them, they look like “dd-mm-yyyy” in the formula bar. When I then do my search for the date with the inputbox entry, it throws back to me the message alert that the said date from the inputbox is not found, meanwhile the dates I have entered into the inputbox are found in column B. I have the feeling; I need to add some formatting to the “searchdate” variable. Yet I have no idea where to place that guess. I want bigger minds point it out for me. Thanks for having a look.
Code:
Sub Lookup_Dated_Record()
SearchDate = InputBox("Enter date [dd-mm-yy]", , Format(Date, "dd-mm-yy"))
If SearchDate = "" Then Exit Sub
  ListBox1.ColumnCount = 9    
myArray = Sheets("Sheet1").[A4].Resize(,ListBox1.ColumnCount + 1).Value   
 ListBox1.List = myArray    
ListBox1.Clear       
 With Sheets("Sheet1").[B4:B104]            
Set rngFind = .Find(what:=SearchDate, After:=Sheets("Sheet1").[B104], LookIn:=xlValues, Lookat:=xlPart, searchdirection:=xlNext)                If Not rngFind Is Nothing Then        
strFirstFind = rngFind.Address   
 Do   
 If rngFind.Row > 1 Then     
   ListBox1.AddItem Trim(rngFind.Offset(, -1).Text)    
For i = 1 To 8       
 ListBox1.List(ListBox1.ListCount - 1, i) = Trim(rngFind.Offset(, i - 1).Text)    
Next i    
End If    
Set rngFind = .FindNext(rngFind)   
 Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind    
Else        
MsgBox "No match found for: " & " ' " & CDate(SearchDate) & " ' ", vbInformation    
End If    
End With
End Sub
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Crystalyzer

Board Regular
Joined
Oct 18, 2011
Messages
177
The INPUTBOX returns a string. Have you tried this?

Code:
SearchDate = CDate(InputBox("Enter date [dd-mm-yy]"))
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
The INPUTBOX returns a string. Have you tried this?

Code:
SearchDate = CDate(InputBox("Enter date [dd-mm-yy]"))
Okay cool, when I did that some of the dates returned a search result into the list box example "14-09-18".

But some failed to return result. Example " 22-10-19", "15-10-18"
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
So I went on digging to find solutions to my case and I came this close. All I need now is someone to tidy up things for me. The code down here is supposed to pull dates in a given month on a/the year. So I did something I may be tempted to call a smart tweak and came to this. Now what I want this code do, instead of pulling out a given month record, I want to pull just the date I place in the inputbox.


Thanks again for your time with me.


Code:
Sub Lookup_Dated_Record()
    Dim Crit1$, Crit2$, rng As Range, r&, c&, LBox As Object
    Set LBox = ListBox1
  
SearchDate = CDate(InputBox("Enter date [dd-mm-yy]"))
If SearchDate = "" Then Exit Sub




    With Sheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A4:I" & lr)
        Crit1 = ">=" & Format(DateSerial(Year(SearchDate), Month(SearchDate), Day(SearchDate)), "mm/dd/yyyy")
        Crit2 = "<" & Format(DateSerial(Year(SearchDate), Month(SearchDate) + 1, Day(SearchDate)), "mm/dd/yyyy")
        rng.AutoFilter field:=2, Criteria1:=Crit1, Operator:=xlAnd, Criteria2:=Crit2
    End With
    
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
    
        If Application.CountA(rng.Offset(1).SpecialCells(xlCellTypeVisible)) > 0 Then
        Set rng = rng.Offset(1).SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
        End If
    End With
    With Sheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
I think I just fixed it:
Code:
Crit2 = "<=" & Format(DateSerial(Year(SearchDate), Month(SearchDate), Day(SearchDate)), "mm/dd/yyyy")
Still if there are cooler ways, I am open for them
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,818
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top