Infinite Loop Debug in Vba Using. Findnext Method

Infinite Loop Debug in Vba Using. Findnext Method

I'm learning VBA and I stuck with a loop while using .FindNext method. I tried many ways to fix the loop but what ever I do I end up in an infinite loop, and you know how annoying this can be when you coding in excel. Please, if anyone can fix my code would be a great Help!

Private Sub cbGO_Click()

    Dim ws As Worksheet, OutputWs As Worksheet
    Dim rFound As Range
    Dim strName As String
    Dim count As Long, LastRow As Long
    Dim IsValueFound As Boolean


    IsValueFound = False
    Set OutputWs = Worksheets("Summary")    '---->change the sheet name as required
    LastRow = OutputWs.Cells(Rows.count, "A").End(xlUp).Row

    On Error Resume Next
    strName = ComboBox1.Value
    If strName = "" Then Exit Sub
    For Each ws In Worksheets

        If ws.Name <> "Lists" And ws.Name <> "Summary" Then

            With ws.UsedRange

                Set rFound = .Find(What:=strName, LookAt:=xlWhole)
                If Not rFound Is Nothing Then
                    firstAddress = rFound.Address

                    Do

                    rFound.EntireRow.Cells(1, "B").Resize(1, 4).Copy
                    OutputWs.Cells(LastRow + 1, 1).PasteSpecial xlPasteAll
                    Application.CutCopyMode = False
                    LastRow = LastRow + 1
                    Set rFound = .FindNext(rFound)

                    Loop While Not rFound Is Nothing And rFound.Address <> fristAddress

                End If
            End With
        End If
    Next ws
    On Error GoTo 0
    If IsValueFound Then
       OutputWs.Select
       MsgBox "Result pasted to Sheet Output"
    Else
        MsgBox "Value not found"
    End If

End Sub
8

1 Answer

you need to write "firstAddress", not "fristAddress". :D

Loop While Not rFound Is Nothing And rFound.Address <> fristAddress

After fixing this typo, it should work.

Another note: you are not setting IsValueFound to True yet.

Edit: oh ok, others were faster to spot it :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

James H. Sterling
Author

James H. Sterling

James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.