Run-time Error 91 - Object Variable or with Block variable not set - VBA

Question

I have the following snippet VBA:

DatetoRun = InputBox("How far back do you want to run the MI for?", "Date backing", Format(Now(), "dd/mm/yyyy"))

'Date validation for DatetoRun
If IsDate(DatetoRun) Then       
    DatetoRun = Format(CDate(DatetoRun), "dd/mm/yyyy")
    dateStart = DatetoRun        
Else
    MsgBox "Please enter a DATE in the correct format dd/mm/yyyy"
End If

Set found = Range("A:A").Find(what:=DatetoRun, LookIn:=xlValues, lookat:=xlWhole)

iclearrow = found.Row

When executing it I keep getting the

Object Variable or With Block variable not set

which confuses me as I am setting it:

Set found = Range("A:A").Find(what:=DatetoRun, LookIn:=xlValues, lookat:=xlWhole)

The error happens on the last row:

iclearrow = found.Row

Not sure how or why this is happening. also sometime it will randomly work once, but then error out after. Nothing is consistent!

Shai Rado Shai Rado · Accepted Answer · 2018-04-16T10:51:21

You need to have error handling scenario if Find failed to find DatetoRun in column A.

Set found = Range("A:A").Find(what:=DatetoRun, LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then ' find was successfull
    iclearrow = found.row
Else ' Find failed >> raise an error
    MsgBox "Unable to find " & DatetoRun, vbCritical
End If

Note: when searching for dates, they are actually stored as numeric values, so try using:

Set found = Range("A:A").Find(what:=CDbl(DatetoRun), LookIn:=xlValues, lookat:=xlWhole)

Edit 1: Try the following line of Find :

Set found = Range("A:A").Find(what:=DatetoRun, LookIn:=xlFormulas, lookat:=xlPart)

Run-time Error 91 - Object Variable or with Block variable not set - VBA

2 Answers