VBA Run-Time Error 91 - 2

Question

I have a macro which works for the first 36 rows but then shows a run-time 91 error. It shows the error in find statement. The purpose of the macro is to calculate the 90th percentile of the figures populated in a column, count the number of values which are equal or greater the percentile, and provide the division across various departments. Can anybody please help me to correct the error?

For bb = 1 To temcnt
cc = Sheets("tem").Cells(bb, ttc + 4).Value
Sheets("Geographic Strength").Activate
Cells.Find(What:=cc, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    ff1 = ActiveCell.Column
    Sheets("tem").Activate
    rnggg = Range(Cells(2, 6), Cells(ttr, 6))
    mamm = WorksheetFunction.CountIf(Range(Cells(2, 6), Cells(ttr, 6)), cc)
Sheets("geographic strength").Activate
f222 = Sheets("individual strength").Cells(1, iii).Value

**Cells.Find(What:=f222, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    f333 = ActiveCell.Row**

'Error is in the above statement(Cells.Find)

Cells(f333, ff1).Value = mamm
Next bb
Sheets("tem").Delete
Next iii
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Using `.Activate`, `.Select` etc is generally a bad idea because it slows down the macro and increases the risk of mistakes. [Here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and [here](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9292/avoid-using-select-or-activate) are some tips on how to avoid it. — arcadeprecinct, Oct 17 '16 at 06:45

score 1 · Accepted Answer · answered Oct 17 '16 at 06:42

it's because

Cells.Find(What:=f222, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)

didn't succeed in finding the wanted cell so it returns Nothing, and you can't Activate Nothing

so you could go like follows:

'... your code before
f222 = Sheets("individual strength").Cells(1, iii).Value

Dim found As Range
Set found = Cells.Find(What:=f222, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not found Is Nothing Then '<-- check if any cell has been found
    found.Activate
    f333 = ActiveCell.Row

    '... rest of your code should f222 have been found

End If

' rest of your code

Thanks a lot for your help. It is working perfectly now. :) – Jagpreet Singh Bhatia Oct 17 '16 at 08:09 — Jagpreet Singh Bhatia, Oct 17 '16 at 08:09

VBA Run-Time Error 91 - 2

1 Answers1