- Apr 10, 2017
- Office Version
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