infinite loop debug in VBA using .FindNext method

Question

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

Spelling mistake `fristAddress` vs `firstAddress`. (Used `Option Explicit` and declare _all_ variables to avoid this type of error) — chris neilsen, May 03 '16 at 08:05
Beat me to it ! by a second, also some brackets to make sure the grouping/precedence of that not and and are what you really want. But I suspect the spelling mistake is the issue. — NanoTera, May 03 '16 at 08:05
I'm sorry, but I don't understand. I declared fristAdress, but still not working. Can anyone please answer the question by writing the code to inspect it? — Lóránt Csaba Mihály, May 03 '16 at 08:15
@Ralph , I'm sure there is no misspelling error. Especially if I commenting the line where the firstAdress occurs. — Lóránt Csaba Mihály, May 03 '16 at 08:18
and theyre right with the spelling issue. "rFound.Address <> fristAddress" you declared FIRST not FRIST. so your loop is set to continue while rFound is not FRISTAddress... Which doesnt exist. — DDuffy, May 03 '16 at 08:23

Sun · Accepted Answer · 2016-05-03T11:36:05.600

2

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 :)

edited May 03 '16 at 11:36

answered May 03 '16 at 08:51

Sun

762
4
10

infinite loop debug in VBA using .FindNext method

1 Answers1

Linked