VBA Run time error 91. Trouble setting object variable debug

Question

I have taken over someone else's macro who has left the organisation. I get the following error as listed in the title with Run time error.

Below is the code it is telling me to debug. Problem is this is my first time to VBA macro's and I am unsure where to begin to solve the error.

Any help would be great as I can't go past this point.

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

Possible duplicate of Excel VBA range.find acting up

Mathieu Guindon
– Mathieu Guindon

2016-07-21 06:22:46 +00:00
Commented Jul 21, 2016 at 6:22 — Mathieu Guindon
– Mathieu Guindon, Commented Jul 21, 2016 at 6:22

Tim Williams · Accepted Answer · 2016-07-21 06:16:56Z

1

If the value is not found in the search range then you will get an error, so it's best to split up your code into separate operations:

Dim f As Range
Set f = Cells.Find(What:="Top 10 Rank", After:=ActiveCell, LookIn:=xlValues, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, _
                   SearchDirection:= xlNext, MatchCase:=False, _
                   SearchFormat:=False)

If Not f Is nothing Then
    'do somthing with f
Else
    Msgbox "not found!"
End If

answered Jul 21, 2016 at 6:16

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

@MarkMeiring on Stack Overflow (and Stack Exchange in general) the best "thank you" is a checkmark. See that green checkmark icon under the up/down voting buttons? Give it some lovin', you'll get +2 for it and tell the world "this! this right here, look! this is the answer!"

Mathieu Guindon · Accepted Answer · 2016-07-21 06:27:47Z

1

No data was found, so .Find returned a null object reference (Nothing in VBA), as explained in this Microsoft Support page:

This macro error occurs because the Visual Basic Find method returns a NULL value which makes activating a cell impossible.

edited Jul 21, 2016 at 6:27

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Jul 21, 2016 at 6:18

Clon

1,0658 silver badges12 bronze badges

Collectives™ on Stack Overflow

VBA Run time error 91. Trouble setting object variable debug

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related