Reputation: 31
I am trying to highlight a row based on a number present in a specific cell of that row.
Sub searchTest()
Dim firststepaddress As String
Dim i As Long
For i = 1 To 12
Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate 'error at this line
firststepaddress = ActiveCell.Address
Call cellRangeHighlighter
MsgBox i
Next i
I get
runtime error 91
After clicking debug, the below code is highlighted:
For i = 2 To 12
Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
What I tried:
Attempt 1:
I removed the For
loop and entered the number manually like 1 (What:=1
) or 2 (What:=2
) and so on and the code works.
Attempt 2:
Selection.Find(What:="" & i, After.....) 'still the same error message
Expected Output:
The Selection.Find
function should be able to understand the number present in i
and then proceed to call cellrangehighlighter
sub-procedure.
Actual Output:
It highlights the row containing cell with number "1" and then throws the error.
If I change the range in the For
loop from 2 to 12, then it highlights row having cell containing 2 and then throws the error.
Upvotes: 0
Views: 337
Reputation: 166980
Here's the typical approach to using Find:
Dim sel as Range, f As Range
Set sel = Selection
For i = 2 To 12
Set f = sel.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If Not f Is Nothing Then
'do something with f
Else
'handle not found if needed
End if
Always set the result to a variable, then check it's not Nothing
before proceeding. And avoid Active/Select as much as possible (ie. almost always)
Upvotes: 1