CODEWITHSUNDEEP
vbaexcelruntime-error
Jagpreet Singh Bhatia
Jagpreet Singh Bhatia

Reputation: 13

VBA Run-Time Error 91 - 2

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

Upvotes: 0

Views: 51

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

Related Questions