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,890
Office Version
  1. 2016
Platform
  1. 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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Crystalyzer

Board Regular
Joined
Oct 18, 2011
Messages
185
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,890
Office Version
  1. 2016
Platform
  1. 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,890
Office Version
  1. 2016
Platform
  1. 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,890
Office Version
  1. 2016
Platform
  1. 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,129,790
Messages
5,638,326
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top