Forum Discussion

Wardd's avatar
Wardd
Occasional Reader
Nov 21, 2025

Search for a date in a row does not work in VBA with ".find" when column is too narrow

I have a row with dates (row 3) and a cell with a date (C7)

I want to get the column where this date (C7) is in that row3 

This is my code:

 

Sub find_date()

    Dim datum           As Date
    Dim zoekrij         As String
    Dim cel             As Range
    
    datum = Range("C7").Value
    zoekrij = "A3:Z3"
    

    Set cel = Range(zoekrij).Find(DateValue(datum), LookIn:=xlValues, LookAt:=xlWhole)
     
       
    If Not cel Is Nothing Then
        MsgBox ("Date found in column: " & cel.Column)
    
    Else
        MsgBox ("Date not found")
    End If
      
End Sub

 

This works when the row 3 is formatted as date and the column is wide enough, but when the column is too narrow and shows "####" the date is not found.

I also tried to use Clng(datum) and formatted the row as numeric, but with the same result (works only when column is wide enough)

 

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    for what it is worth, the actual Find function in the spreadsheet (ctrl-f or home -> editing group -> find) works (fails) the same way.

Resources