Help needed to exit a for loop in vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone here,
I need help to exit a nested loop.

With the inner loops, I have been able to get solutions for how to exit them from this same forum.

But I have added a new loop – the outer one and I can’t seem to figure out how to exit it at the moment and I want the geniuses to help me figure out how to exit the outer loop in case the inner ones are exited successfully.


Code:
wStatus = 0
        With Application
            arr(1) = .Trim(freg2.Text)
            V1 = Split(arr(1), " ")
            
            For Each eSheet In Worksheets ‘ How do I exit this loop?
                
                Select Case eSheet.Name
                    Case “Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”
                        elr = eSheet.Cells(Rows.Count, "B").End(xlUp).Row
                        If elr < 4 Then elr = 4
                        For Each sName In eSheet.Range("B4:B" & elr)
                            If Len(.Trim(arr(1))) = Len(.Trim(sName.Text)) Then
                                arr(2) = .Trim(sName.Text)
                                V2 = Split(arr(2), " ")
                                For i = LBound(V1) To UBound(V1)
                                    For j = LBound(V2) To UBound(V2)
                                        If LCase(V1(i)) = LCase(V2(j)) Then
                                            Matches = Matches + 1
                                            If Matches = UBound(V1) + 1 Then
                                                wStatus = 1
                                
                                                Exit For
                                            End If
                                        End If
                                    Next j
                                Next i
                            End If
                            Matches = 0
                        Next sName
                    Case Else
                End Select
                
            Next eSheet
            
        End With
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd probably refactor the code but you could just use a flag variable for each For loop that you need to monitor.
 
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
or just using different kind of loop …​
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
I'd probably refactor the code but you could just use a flag variable for each For loop that you need to monitor.
Hi,​
or just using different kind of loop …​


Different kind of loop, how?

I am always open for cooler ways of doing things.

So you can show me what you have in mind. Thanks
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
         Dim S&
    Do
             S = S + 1
        With Worksheets(S)
            Select Case …
                For …
                    If … Then
                  
                        Exit Do
                    End If
                Next …
            End Select
        End With
    Loop Until S = Worksheets.Count
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
@Marc L ,
I am confused.

Which of the for loops is your sample pointing to?

I see only 1 for loop in your case whereas my sample had 4.

I will be glad if you can show me how to implement it to meet my sample requirement above.

Thanks.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,299
Office Version
  1. 365
Platform
  1. Windows
Another option: use GoTo

Rich (BB code):
           For Each eSheet In Worksheets ' How do I exit this loop?
                
                Select Case eSheet.Name
                    Case “Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”
                        elr = eSheet.Cells(Rows.Count, "B").End(xlUp).Row
                        If elr < 4 Then elr = 4
                        For Each sName In eSheet.Range("B4:B" & elr)
                            If Len(.Trim(arr(1))) = Len(.Trim(sName.Text)) Then
                                arr(2) = .Trim(sName.Text)
                                v2 = Split(arr(2), " ")
                                For i = LBound(v1) To UBound(v1)
                                    For j = LBound(v2) To UBound(v2)
                                        If LCase(v1(i)) = LCase(v2(j)) Then
                                            matches = matches + 1
                                            If matches = UBound(v1) + 1 Then
                                                wStatus = 1
                                
                                                GoTo skip:
                                            End If
                                        End If
                                    Next j
                                Next i
                            End If
                            matches = 0
                        Next sName
                    Case Else
                End Select
                
            Next eSheet
skip:
 

Forum statistics

Threads
1,141,069
Messages
5,704,105
Members
421,327
Latest member
Msh

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
Top