Need code modifying to search date in custom format

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
Hi

I was kindly given the below code a couple of months ago in this thread, that searches for a date I input when I double click a cell in Col A I and it works perfectly.
VBA Code:
Sub FindDate()
 
    Dim myInput   As Variant
    Dim CellFound As Variant
    Dim ws        As Worksheet
         
    Select Case ActiveSheet.Name
     
        Case "Training Log", "Training 1981-1997", "Indoor Bike"
Retry:
            myInput = Application.InputBox("Enter Date:", "Locate Log Entry Date", Type:=2)
            If myInput = "" Or myInput = "False" Then
                'MsgBox "Search cancelled!", vbInformation, "Locate Log Entry Date"
                Exit Sub
            ElseIf Not IsDate(myInput) Then
                MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"
                GoTo Retry
            End If
         
            Select Case ActiveSheet.Name
         
                Case "Training Log", "Training 1981-1997"
         
                    For Each ws In Sheets(Array("Training Log", "Training 1981-1997"))
                        CellFound = Application.Match(CLng(CDate(myInput)), ws.Range("A:A"), 0)
                        If Not IsError(CellFound) Then Exit For
                    Next ws
                 
                Case "Indoor Bike"
                    Set ws = ActiveSheet
                    CellFound = Application.Match(CLng(CDate(myInput)), ws.Range("A:A"), 0)
                 
            End Select
         
            If Not IsError(CellFound) Then
                Application.GoTo ws.Range("A" & CellFound)
            Else
                MsgBox "Sorry, no entry found for " & myInput, vbInformation, "No Match Found"
            End If
         
        Case Else
            MsgBox "The Locate Entry Date function will only run on:" & _
                   vbLf & "    Training Log" & _
                   vbLf & "    Training 1981-1997" & _
                   vbLf & "    Indoor Bike", _
                   vbInformation, "Invalid Sheet"
         
    End Select
 
End Sub
However, I have had to change the date format of sheet 'Training 1981-1997' to the (custom number) format d/m/yy dddd and unsurprisingly, the search won't work for that sheet with the original code.

I'd be very grateful, if it's possible, for an amendment to the above code that will allow the search function to continue working in this sheet.

Many thanks!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you explain in more detail what "The search won't work" means? What exactly happens when you run the code?
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
Sorry Joe, it's easy to forget that the helper hasn't seen the workbook.

When I enter a date in the box, even when it's valid and should be located, it returns the line
VBA Code:
MsgBox "Sorry, no entry found for " & myInput, vbInformation, "No Match Found"
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you step through the code what's the value of CLng(CDate(myInput) when you've executed this line:
VBA Code:
CellFound = Application.Match(CLng(CDate(myInput)), ws.Range("A:A"), 0)
Have you tried changing the type on the inputbox from 2 (text) to 1 (number)?
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry Joe, I'm not familiar with stepping through. I selected Debug then kept keying F8 until the input box opened. I then had to input a date and it then returned to the start of the code. However, when I mouseover'd the line you referred to, it said "CellFound = error2042". Hope that helps?

When I changed the type on the inputbox from 2 to 1, it returned
VBA Code:
MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry Joe, I'm not familiar with stepping through. I selected Debug then kept keying F8 until the input box opened. I then had to input a date and it then returned to the start of the code. However, when I mouseover'd the line you referred to, it said "CellFound = error2042". Hope that helps?

When I changed the type on the inputbox from 2 to 1, it returned
VBA Code:
MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"
when you enter the date in the input box what exactly does the entry look like?
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is before I enter the data

1637377205157.png


This is the data entry:

1637377290119.png


And this is the result (with the valid date in Col A):

1637377365231.png
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try changing the type for the inputbox to a 1 and enter the date as: #20/4/85#
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
No luck I'm afraid Joe

1637378020110.png
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
It works with the standard Find and Replace function, but I guess you knew that?

1637379194086.png
 

Forum statistics

Threads
1,148,393
Messages
5,746,441
Members
424,019
Latest member
dpteo

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