VBA: Object variable or With block variable not set (Error 91) - ???

[Mac Lingo]

I am executing the following statement in VBA:

SetPtr = "Part 1"
Cells.Find(What:=SetPtr, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

And I get the following message:
Runtime Error 91:
Object Variable or With Block Variable not set.

What does this mean?

Thanks,
Mac Lingo
Berkeley, CA

 

[Dave Peterson]

If "Part 1" wasn't found, then you're trying to activate something that doesn't
exist--and it blows up.

The more usual approach:

dim FoundCell as range
SetPtr = "Part 1"
set foundcell = Cells.Find(What:=SetPtr, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

if foundcell is nothing then
msgbox "Not found"
else
foundcell.activate
end if

 

[Rowan Drummond]

It means that the text "Part 1" was not found. You can avoid this error
by setting a range object when the value is found eg:

Dim SetPtr As String
Dim fndRng As Range
SetPtr = "Part 1"
Set fndRng = Cells.Find(What:=SetPtr)
If Not fndRng Is Nothing Then
fndRng.Select 'or whatever
End If

Hope this helps
Rowan

 

[Norman Jones]

Hi Mac Lingo,

The find method returns a range object which needs to be set.

Try something like:

Dim SetPtr As String
Dim rng As Range

SetPtr = "Part 1"

Set rng = Cells.Find(What:=SetPtr, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
'Your code
End If

 

[Norman Jones]

The find method returns a range object which needs to be set.

Should read:

The find method returns a range object which needs to be set to a range
variable which can be tested; or else you need to use an error handler to
prevent the error which results if the search string is not located.

The code was fine, the explanation was prematurely truncated!