Need code modifying to search date in custom format

[Ironman]

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.

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]

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)?

 

[Ironman]

When I copied and pasted the date format (e.g. 24/4/85 WEDNESDAY) with type argument set to 2 I got

MsgBox "Only enter a valid date format.", vbExclamation, "Invalid Date Entry"

 

[JoeMo]

When I copied and pasted the date format (e.g. 24/4/85 WEDNESDAY) with type argument set to 2 I got

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]

Hi Joe

FALSE.

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

 

[JoeMo]

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]

Sure

DateSearchTest.xlsm
	A
1574	22/4/85 MONDAY
1575	23/4/85 TUESDAY
1576	24/4/85 WEDNESDAY
1577	25/4/85 THURSDAY
1578	26/4/85 FRIDAY
1579	27/4/85 SATURDAY
1580	28/4/85 SUNDAY
1581	29/4/85 MONDAY
1582	30/4/85 TUESDAY
1583	1/5/85 WEDNESDAY
1584	2/5/85 THURSDAY
1585	3/5/85 FRIDAY
1586	4/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?

 

[JoeMo]

Sure

DateSearchTest.xlsm
	A
1574	22/4/85 MONDAY
1575	23/4/85 TUESDAY
1576	24/4/85 WEDNESDAY
1577	25/4/85 THURSDAY
1578	26/4/85 FRIDAY
1579	27/4/85 SATURDAY
1580	28/4/85 SUNDAY
1581	29/4/85 MONDAY
1582	30/4/85 TUESDAY
1583	1/5/85 WEDNESDAY
1584	2/5/85 THURSDAY
1585	3/5/85 FRIDAY
1586	4/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]

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" 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.

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]

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

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]

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"