excel - VBA, goto cell with a certain value (type: date)

Question

I have code as follow:

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = Range("A1") 
    If Trim(FindString) <> "" Then
        With Sheets("Kalendarz").Range("A5:LY5")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

but it doesn't work with date format, any suggestion?

More details: In A1 cell I will enter a date, In 5. row I have a list of every day in 2016. I want to (after run macro) go to cell with date from cell A1.

Answer 1

Using the Find function to locate a date is notoriously tricky in Excel VBA. The function relies on your search date being formatted in the same way as Excel's default setting. In addition, you need to ensure that the search string is converted to a date within the Find function, using CDate. Ozgrid has a good article on it: http://www.ozgrid.com/VBA/find-dates.htm

I have amended your code below to accommodate those requirements and added an extra With Sheets... statement to ensure the FindString uses the Range from your target sheet.

However, because of the unpredictability of users' adjusting date formats, I prefer a VBA loop, using Value2 which is Excel's numerical representation of the date, so cannot be fiddled with. The Ozgrid article prefers not to use VBA loops when a Find function is so much faster, but I guess it's a matter of personal preference and I feel a bespoke loop is more reliable.

Up to you which one you want to go with.

The Find method:

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range

    With Sheets("Kalendarz")
        FindString = .Range("A1")
        If Trim(FindString) <> "" Then
            With .Range("A5:LY5")
                Set Rng = .Find(What:=CDate(FindString), _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If
    End With
End Sub

The VBA loop method:

Sub Find_First_VBA_Loop()
    Dim dateVal As Long
    Dim cell As Range

    With Sheets("Kalendarz")
        dateVal = .Range("A1").Value2
        For Each cell In .Range("A5:LY5").Cells
            If dateVal = cell.Value2 Then
                Application.Goto cell, True
                Exit For
            End If
        Next
    End With

End Sub