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

[kelly mort]

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.

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

 

[Crystalyzer]

The INPUTBOX returns a string. Have you tried this?

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

 

[kelly mort]

The INPUTBOX returns a string. Have you tried this?

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]

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.

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]

I think I just fixed it:

Crit2 = "<=" & Format(DateSerial(Year(SearchDate), Month(SearchDate), Day(SearchDate)), "mm/dd/yyyy")

Still if there are cooler ways, I am open for them