VBA运行时错误91 - 2
问题描述:
我有一个宏为前36行工作,但然后显示运行时91错误。它显示find语句中的错误。宏的目的是计算列中数据的第90百分位数,计算等于或大于百分位的值的数量,并提供各部门之间的划分。任何人都可以帮我纠正错误吗?VBA运行时错误91 - 2
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
答
这是因为
Cells.Find(What:=f222, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
没有找到想要的细胞,因此返回Nothing
成功,你不能Activate
Nothing
,所以你可以去像如下:
'... 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
+0
非常感谢您的帮助。它现在正在完美工作。 :) –
使用'.Activate','.Select'等通常是一个坏主意b因为它会放慢宏观并增加犯错的风险。 [Here](http://*.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)和[here](http://*.com/documentation/excel -vba/1107/vba-best-practices/9292/avoid-using-select-or-activate)是如何避免它的一些提示。 – arcadeprecinct