Error reading range

[AlexanderBB]

Look = xlComments
SearchPart = xlPart
rCol= "C2:C58"
Set Found = Range(rCol).Find(What:=TextBox1.Text, lookIn:=Look, Lookat:=SearchPart)
If Not Found Is Nothing Then
    Set FirstFound = Found
    Do
        Debug.Print Found, Found.Address
        Set Found = Range(rCol).FindNext(After:=Found)
    Loop Until FirstFound.Address = Found.Address
End If

The last address it prints is C59. That's one more than the range should have and I get error
Run-time error '1004':
Unable to get the FindNext property of the Range class

But if rCol is "C:C" there is no error.
Is how I'm defining the rows or range wrong?

 

[DanteAmor]

Change this line:

Loop Until FirstFound.Address = Found.Address

For this:

Loop Until FirstFound.Address = Found.Address Or Intersect(Range(rCol), Found) Is Nothing

 

[DanteAmor]

Or Intersect(Range(rCol), Found) Is Nothing

Doing more tests I found that this applies only when you use xlComments in the Lookin parameter. Because when you use xlvalues it is not necessary.

 

[MARK858]

I found that this applies only when you use xlComments in the Lookin parameter

Probably also applies to xlCommentsThreaded (I haven't tested it yet and is only in later versions of Excel). xlFormulas you also don't need it with.

 

[AlexanderBB]

Thanks Dante, very glad to have a solution. I only just found you can search comments like this and for what I'm doing it's brilliant.
LookIn can alternate between xlValues and xlComments and your fix handles both.