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](http://stackoverflow.com/questions/21403916/excel-vba-range-find-acting-up) — Mathieu Guindon, Jul 21 '16 at 06:22

score 1 · Answer 1 · answered Jul 21 '16 at 06:16

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 '16 at 06:16

Tim Williams

154,628
8
97
125

@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 Jul 21 '16 at 07:58

score 1 · Answer 2 · edited Jul 21 '16 at 06:27

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 '16 at 06:27

Mathieu Guindon

69,817
8
107
235

answered Jul 21 '16 at 06:18

Clon

1,035
8
12

VBA Run time error 91. Trouble setting object variable debug

2 Answers2