VBA Excel error object variable or with block variable not set

Question

The code below will find adjacent word but I tried to add an error handler if a word is not in the spreadsheet. I'm getting error object variable or with block variable not set. What is the problem? or can you help fix the error message so that if the word is not found the msgbox displays MsgBox "Sorry the text was not found please try again. Macro stopping". Thanks!

Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
Dim Number  As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "report" 'word not in spreadsheet
Set match = ws.Cells.Find(findMe)
findOffset = match.Offset(, 1).Value 'error occurs object variable or with block variable not set on this line
Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findMe & """,A1:AZ96,1)))))")

If (Not match Is Nothing) Then

     MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. It is found "" " & Number & """ times!"

 Else
    'not match
     MsgBox "Sorry the text was not found please try again. Macro stopping"

 End If

End Sub

you need the if not match is nothing line before the findOffset = match.Offset(, 1).Value line — JosieP
@JosieP is correct. Your error results when match Is Nothing. — David Zemens

Julien Marrec Julien Marrec · Accepted Answer · 2013-07-02T16:03:18

As JosieP said, This code works as intended (I just tried it)

    Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
Dim Number  As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "report" 'word not in spreadsheet
Set match = ws.Cells.Find(findMe)

If (Not match Is Nothing) Then

    findOffset = match.Offset(, 1).Value 'error occurs object variable or with block variable not set on this line
    Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findMe & """,A1:AZ96,1)))))")
    MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. It is found "" " & Number & """ times!"

 Else
    'not match
     MsgBox "Sorry the text was not found please try again. Macro stopping"

 End If

End Sub

VBA Excel error object variable or with block variable not set

1 Answers