I've been trying to find a date in a range, but for some reason the find method just doesnt work sometimes.

The dates in the range are formatted as such, each in two merged cells and in format "mmm-yy", in Spanish. They are a mix of just values and formulas, they're values up until april 21 (they all are the first of the month), and from may-21 onwards they all use =EOMONTH with the previous cell. This is a problem from a larger code (I don't generate the dates, I just want to find one), but the specific part that I'm interested is this:

 Sub tset()

    Dim cell As Range
    Set cell = Selection.Find("ene-22", _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            MatchCase:=False)
    cell.Select
 end sub

I know is not the best practices to use selection, but I'm just using it to try to figure out why is this not working. Now, the problem is this works sometimes, and sometimes it doesn't, it's not consistent at all: in the year 2021 ene (january), abr (april), ago (august), set (september), dic (december) and january again, from this year are all dates that DO NOT work, but all of the rest work just fine and as intended. This doesn't make any sense since it's not like only the formulated ones don't work. To see if the code was at fault, I used the same code to find dates in a column of non merged cells with the same format and both values and formulas, and it worked. I tried to replicate the range of dates (the merged ones) in a new workbook and it shows the exact same inconsistencies. I used find manually and it is able to find every single date. More over, I tried recording a macro to do this, since I was able to use find manually and it was successfully recorded, but when I tried to run the recorded macro it doesn't work ( Object variable or With block variable not set, which is the error I'm always getting in these dates). So far I'm out of ideas, I would really appreciate all the help, thank you.

CodePudding user response：

Find a Date in a Row

• I've written a function to find the date since the Find method gave me grief.
• The key features are that you need to use the day, too, i.e. 1-ene-22, and that it uses Application.Match with CLng and CDate which works even if the cells are merged.
• If there is a match, the function will return a reference to the cell (range). If not, it will return Nothing so, in your procedure, you need to test for Nothing the same way you should do it when using the Find method.

Option Explicit

Sub FindDateInRowTEST()
    
    Const wsName As String = "Sheet1"
    Const FirstDateCellAddress As String = "A1"
    Const DateString As String = "1-ene-22"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim dCell As Range
    Set dCell = FindDateInRow(ws.Range(FirstDateCellAddress), DateString)
    
    If Not dCell Is Nothing Then
        MsgBox "Found the date in cell '" & dCell.Address(0, 0) & "'.", _
            vbInformation
    Else
        MsgBox "Could not find the date.", vbCritical
    End If

End Sub

Function FindDateInRow( _
    ByVal FirstCell As Range, _
    ByVal DateString As String) _
As Range
    Const ProcName As String = "FindDateInRow"
    On Error GoTo ClearError

    Dim rrg As Range
    With FirstCell.Cells(1)
        Dim lCol As Long
        With .Worksheet.UsedRange
            lCol = .Columns(.Columns.Count).Column
        End With
        If lCol < .Column Then Exit Function ' too few columns
        Set rrg = .Resize(, lCol - .Column   1)
    End With
    
    Dim cIndex As Variant
    cIndex = Application.Match(CLng(CDate(DateString)), rrg, 0)
    If IsError(cIndex) Then Exit Function ' date not found
    
    Set FindDateInRow = rrg.Cells(cIndex)

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function