findnext error in loop
 

Hi everyone,
First of all thanks to everyone who has helped me so far.

I have one little quirk in my code and I'm not sure how to fix it. The
following code works fine if there is more than one instance of "Account
Category:*" found in col. K:T, but if there is only one instance found, I get
the error message "Run-time error '91': Object variable or With block
variable not set."

Help! Thank you!


Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet

With wks

Set rngToSearch = Sheets("Sheet1").Columns("K:T")
Set rngFound = rngToSearch.Find(What:="Account Category:*", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address

Do
rngFound.Select
Call ThisWorks2 '<-this macro only formats the cell
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or strFirstAddress = rngFound.Address
Else
End If
End With

findnext error in loop
 

Never mind, I just found it through some more searching in the forums:
Just added "if rngfound is nothing then exit do" right before the loop
command.

"SLW612" wrote:

Hi everyone,
First of all thanks to everyone who has helped me so far.

I have one little quirk in my code and I'm not sure how to fix it. The
following code works fine if there is more than one instance of "Account
Category:*" found in col. K:T, but if there is only one instance found, I get
the error message "Run-time error '91': Object variable or With block
variable not set."

Help! Thank you!


Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet

With wks

Set rngToSearch = Sheets("Sheet1").Columns("K:T")
Set rngFound = rngToSearch.Find(What:="Account Category:*", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address

Do
rngFound.Select
Call ThisWorks2 '<-this macro only formats the cell
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or strFirstAddress = rngFound.Address
Else
End If
End With

findnext error in loop
 

This works fine for me.

Are you sure ThisWorks2 only formats the cell?



In article ,
SLW612 wrote:

Hi everyone,
First of all thanks to everyone who has helped me so far.

I have one little quirk in my code and I'm not sure how to fix it. The
following code works fine if there is more than one instance of "Account
Category:*" found in col. K:T, but if there is only one instance found, I get
the error message "Run-time error '91': Object variable or With block
variable not set."

Help! Thank you!


Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet

With wks

Set rngToSearch = Sheets("Sheet1").Columns("K:T")
Set rngFound = rngToSearch.Find(What:="Account Category:*", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address

Do
rngFound.Select
Call ThisWorks2 '<-this macro only formats the cell
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or strFirstAddress = rngFound.Address
Else
End If
End With