Unable to get the FindNext property

[canyflyer]

Hi All!

I am in dire need of all your expert advise.

I have already searched through google and previous forums here with similar problems but none of the answers in those forums seemed to indicate why my code is not working...

I have even compared to the docs.microsoft.com example and it is identical!

I have a form with a combo box and when selecting a name in it it looks through a sheet and returns all the instances of the name.

The below code gets through the first loop and displays a name in the msgbox but then on the next line it goes bonkers at me and states:

"Error 1004 - Unable to get the FindNext property of the Range class"

Private Sub cbxName_Change()
    Dim rfind As Range
    Dim fAdd As String
    Dim cName As String
    
    cName = cbxName.value
    
    With Sheets("Invoice Sched").Range("C:C")
        Set rfind = .Find(cName, LookIn:=xlValues)
        If Not rfind Is Nothing Then
            fAdd = rfind.Address
            Do
                MsgBox (rfind.value)
                Set rfind = .FindNext(cName)
            Loop While Not rfind Is Nothing And rfind.Address <> fAdd
        End If
    End With
    
    
End Sub

Please help!!

 

[canyflyer]

Hi!

So I figured out the problem! LoL!

I decided not to delete the thread but rather let others learn from my stupidity.

In the FindNext function you are supposed to send it a range object not a string - my mind was telling me FindNext(string-that-I-want-to-find) but it actually should read like this:

Private Sub cbxName_Change()
    Dim rfind As Range
    Dim fAdd As String
    Dim cName As String
    
    cName = cbxName.value
    
    With Sheets("Invoice Sched").Range("C:C")
        Set rfind = .Find(cName, LookIn:=xlValues)
        If Not rfind Is Nothing Then
            fAdd = rfind.Address
            Do
                MsgBox (rfind.value)
                Set rfind = .FindNext(rfind)
            Loop While Not rfind Is Nothing And rfind.Address <> fAdd
        End If
    End With
    
    
End Sub

 

[jb07060]

This is helpful, but I still get an error: After going through the while Loop, Excel eventually runs out of the items to find.
Then, rfind is Nothing.
However, instead of exiting the Loop While, it throws an error. When I run the debugger, I can mouse over the term "rfind.Address"
and I see the error is: Object Variable or With block variable not set. So when rfind is Nothing, Excel Office 365 seems to not evaluate
the rfind.Address part of the While clause.
Any ideas?

thanks,
John

 

[Fluff]

The code in post#2 works for me.
Are you actually using that code, or something else?

 

[jb07060]

No, not exactly that code, because I had removed the With construct. At the time I was getting the error, I thought an error when rfind is = Nothing seems reasonable,
and I didn't see how that would evaluate.
However, after redoing everything, it seems to work now. So, the problem is solved for the short term. For the long term, I'll have to spend some time
figuring out what Nothing.address evaluates to. (Newbie at this stuff....)
thanks,
John

 

[Fluff]

You cannot do Nothing.Address because Nothing is just that, nothing, nada, zilch, sod all etc.
If you post the code the are using, I can have a look at it.