Forum Discussion
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_tarlerBronze 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.