Results 1 to 2 of 2

Thread: Compile error: Loop without Do
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    189
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Compile error: Loop without Do

    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.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Compile error: Loop without Do

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •