Need code modifying to search date in custom format

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
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!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,697
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The problem is that the Application.Match line is not finding a match, causing CellFound to be an error. What happens if you copy a date cell from col A and paste it to the inputbox (with the type argument set to 2)?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
When I copied and pasted the date format (e.g. 24/4/85 WEDNESDAY) with type argument set to 2 I got
VBA Code:
MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,697
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
When I copied and pasted the date format (e.g. 24/4/85 WEDNESDAY) with type argument set to 2 I got
VBA Code:
MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"
That implies that your "dates" in col A don't pass the IsDate test, meaning VBA does not see them as dates. What do you get if you enter in any empty cell: =ISNUMBER(A1) replacing A1 with the address of one of your "date" cells in col A?
 

Ironman

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

FALSE.

If it makes it any easier for you, I've just uploaded the sheet HERE for your convenience?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,697
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Sorry, I don't download files from the web. Can you post a sample of your data using XL2BB?

In any case it appears your code needs no modifying, its the data in col A that's causing the problem. Is the col A data entered manually?
 

Ironman

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

DateSearchTest.xlsm
A
157422/4/85 MONDAY
157523/4/85 TUESDAY
157624/4/85 WEDNESDAY
157725/4/85 THURSDAY
157826/4/85 FRIDAY
157927/4/85 SATURDAY
158028/4/85 SUNDAY
158129/4/85 MONDAY
158230/4/85 TUESDAY
15831/5/85 WEDNESDAY
15842/5/85 THURSDAY
15853/5/85 FRIDAY
15864/5/85 SATURDAY
Training 1981-1997


Yes, I enter it manually. The format of the days was originally lower case and I amended them to upper in another column with the UPPER formula and then copied the values across to Col A. Would this make any difference?

Agggghhh - I've just tested one of those dates with a lower case and it works!!! - it's the upper case that's causing the problem! But I still want to keep that if at all possible?
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,697
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Sure

DateSearchTest.xlsm
A
157422/4/85 MONDAY
157523/4/85 TUESDAY
157624/4/85 WEDNESDAY
157725/4/85 THURSDAY
157826/4/85 FRIDAY
157927/4/85 SATURDAY
158028/4/85 SUNDAY
158129/4/85 MONDAY
158230/4/85 TUESDAY
15831/5/85 WEDNESDAY
15842/5/85 THURSDAY
15853/5/85 FRIDAY
15864/5/85 SATURDAY
Training 1981-1997


Yes, I enter it manually. The format of the days was originally lower case and I amended them to upper in another column with the UPPER formula and then copied the values across to Col A. Would this make any difference?

Agggghhh - I've just tested one of those dates with a lower case and it works!!! - it's the upper case that's causing the problem! But I still want to keep that if at all possible?
Enter your dates in col A manually in format d/m/yy then custom format the cells as d/m/yy dddd. The first letter of the day will be capitalized. I'd settle for that as anything you do to capitalize all letters of the day will probably change your dates to text.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Enter your dates in col A manually in format d/m/yy then custom format the cells as d/m/yy dddd. The first letter of the day will be capitalized. I'd settle for that as anything you do to capitalize all letters of the day will probably change your dates to text.
Many thanks for your perseverance Joe! I think the expression is "ball-ache" :biggrin: though, as there are 6,210 rows to re-enter dates manually, hence the need for code.

To avoid any more hassle I'm going to put the day (dddd) on the next line ('carriage return') in lower case. Would it be possible for you to look at the below code, which puts the "dddd" onto the next line (i.e. not a new row). I was kindly given the below code earlier that worked when the dddd was in upper case, but when I tried it again just now, it returns "Error 9, subscript out of range" and I don't know why, as nothing else has changed.
VBA Code:
Sub test()
Dim a As Variant
Dim i As Long
With ActiveSheet
 For i = 2 To .Range("a" & Rows.Count).End(xlUp).Row
a = Split(Cells(i, 1))
Cells(i, 1) = a(0) & vbCrLf & a(1)
Next
End With
End Sub
Many thanks!
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,922
Office Version
  1. 2019
Platform
  1. Windows
Hi,
should be possible to convert your string dates to real dates

Make back-up of your workbook & with required sheet active, run following code from Standard module

VBA Code:
Sub ConvertStringDate()
    Dim cell    As Range
    Dim strText As String
   
    For Each cell In ActiveSheet.UsedRange.Columns("A").Cells
        strText = Left(cell.Value, InStr(1, cell.Value, " "))
        If IsDate(strText) Then cell.Value = DateValue(strText) : cell.NumberFormat = "d/m/yy dddd"
    Next cell
End Sub

Code may be a little slow but hopefully, will convert your string dates to real dates in the required format

Now using your FindDate code, enter a date in form shown e.g 26/4/85 & see if code now works for you

Dave
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Hi Dave, many thanks for replying.

I ran your code and did the search and it unfortunately still returned "Sorry, no entry found for " & myInput, vbInformation, "No Match Found"
 

Forum statistics

Threads
1,147,921
Messages
5,743,883
Members
423,823
Latest member
Zxcvbnm58

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