Compile error: Loop without Do

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
With ActiveSheet.Range("G:G")        
        Set rSearchRng = .Find(what:=Me.TextBox4.Value, lookat:=xlWhole, LookIn:=xlValues)
        
        If Not rSearchRng Is Nothing Then
            FirstRngCellAddress = rSearchRng.Address
            Debug.Print rSearchRng.Address
            rSearchRng.Select
            Do
                With ActiveSheet.Range("D:D")
                    Set rSearchLotValue = .Find(what:=Me.TextBox1.Value, lookat:=xlWhole, LookIn:=xlValues)


                    If Not rSearchLotValue Is Nothing Then
                        sFirstLotNumAddress = rSearchLotValue.Address
                        Debug.Print sFirstLotNumAddress
                        rSearchLotValue.Select
                    End If
                End With
                sProductCodeValue = rSearchRng.Offset(, -5).Value
                sSearchLotValue = rSearchRng.Offset(, -3).Value
                lProductTotal = 0
                
                Do
                    If rSearchRng.Value = TextBox4.Value And sSearchLotValue = Me.TextBox1.Value And sProductCodeValue = TextBox2.Value Then
                        ActiveCell.Offset(, 4).Select
                        lProductTotal = lProductTotal + ActiveCell.Value
                        ActiveCell.Offset(, 2).Select
                        lProductTotal = lProductTotal + ActiveCell.Value
                        ActiveCell.Offset(, 2).Select
                        lProductTotal = lProductTotal + ActiveCell.Value
                        Debug.Print ActiveCell.Value
                        Set rSearchLotValue = Range("D:D").FindNext(rSearchLotValue)
                        rSearchLotValue.Select
                        Debug.Print sFirstLotNumAddress
                        Debug.Print rSearchLotValue.Address
                    Else
                        GoTo continue
                    End If
                Loop While rSearchRng.Value = TextBox4.Value And sSearchLotValue = Me.TextBox1.Value And sProductCodeValue = TextBox2.Value And rSearchLotValue.Address <> sFirstLotNumAddress
                Set rSearchRng = .FindNext(rSearchRng)
            If rSearchRng Is Nothing Then
                Exit Do
            Else
                Loop While Not rSearchRng Is Nothing And rSearchRng.Address <> FirstRngCellAddress
            End If
continue:
        ElseIf rSearchRng Is Nothing Then
            MsgBox "The work order number " & frmSDPFLineSelect1.TextBox4.Value & " was not found for the " & sLineName & " line. Please confirm that the correct production line was selected and try again.", vbCritical, "Incorrect Selection"
            ActiveWorkbook.Close
            frmSDPFLineSelect1.Show
        End If
    End With
I included the entire code to help answer any questions. The problem occurs on these lines of code.
Code:
Set rSearchRng = .FindNext(rSearchRng)            If rSearchRng Is Nothing Then
                Exit Do
            Else
                Loop While Not rSearchRng Is Nothing And rSearchRng.Address <> FirstRngCellAddress
            End If
When the program gets to this line
Code:
Set rSearchRng = .FindNext(rSearchRng)
and there is no more rSearchRng values it sets rSearchRng to Nothing. As you can see I placed the second Loop while inside an If statement which is probably the reason for this error because when I remove the 'If..Else..EndIf and select Debug and compile VBA project I no longer get this error. Unfortunately, I really need this conditional statement in there for this to work how I want it to. Any help would be greatly appreciated. Thank You.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You can't put a Loop inside an If...End If like that. Also, the fact you're using Goto is usually a hint that you should restructure what you're doing.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top