none
how to break out of an outer For Loop from within an inner For Loop?

    Question

  • Dim rng As Range, i As Integer, j As Integer
    Set rng = Sheet1.UsedRange
    For i = 1 to rng.Rows.Count
      For j = 1 to rng.Columns.Count
        If rng(i,j) = "textX" Then
          Debug.Print rng(i,j)
          Exit For
        End If
      Next
    Next

    Is there a way in VBA to break out of an outer For Loop as above from within the inner For Loop without having to add like a boolean var and a few more IF statements in the outer For Loop?  And also not using a GoTo lblwhatever?   Or am I looking at GoTo lblwhatever which would be outside of the outer For Loop?

    Thanks

     

    Friday, October 22, 2010 4:55 PM

Answers

  • OK.  I went with the boolean idea.  It looks like this:

    Dim rng As Range, i As Integer, j As Integer
    Dim bBreakOut As Boolean
    bBreakOut = False
    Set rng = Sheet1.UsedRange
    For i = 1 to rng.Rows.Count
       For j = 1 to rng.Columns.Count
          If rng(i,j) = "textX" Then
             Debug.Print rng(i,j)
             bBreakOut = True
             Exit For
           End If
       Next
       If bBreakOut = True Then Exit For
    Next

    I guess it's not that bad.

     

    • Marked as answer by Bessie Zhao Monday, November 01, 2010 9:30 AM
    Friday, October 22, 2010 5:35 PM
  • I've always done something like this:

    Dim rng As Range, i As Integer, j As Integer
    Set rng = Sheet1.UsedRange
    For i = 1 to rng.Rows.Count
     For j = 1 to rng.Columns.Count
      If rng(i,j) = "textX" Then
       Debug.Print rng(i,j)
    '
    ' --- I'd delete the exit for
    '
    '   Exit For
    ' 
    ' --- here's my code entry
    '
       j=rng.columns.count
       i=rng.rows.count
    
      End If
     Next
    Next
    • Marked as answer by Bessie Zhao Monday, November 01, 2010 9:31 AM
    Friday, October 22, 2010 5:37 PM

All replies

  • GoTo lblwhatever

    would be my choice.

    Exit For is a local exit only, meaning it doesn't carry out through multiple levels of For loops - unless you want to use Exit Sub...


    HTH, Bernie
    Friday, October 22, 2010 5:32 PM
  • OK.  I went with the boolean idea.  It looks like this:

    Dim rng As Range, i As Integer, j As Integer
    Dim bBreakOut As Boolean
    bBreakOut = False
    Set rng = Sheet1.UsedRange
    For i = 1 to rng.Rows.Count
       For j = 1 to rng.Columns.Count
          If rng(i,j) = "textX" Then
             Debug.Print rng(i,j)
             bBreakOut = True
             Exit For
           End If
       Next
       If bBreakOut = True Then Exit For
    Next

    I guess it's not that bad.

     

    • Marked as answer by Bessie Zhao Monday, November 01, 2010 9:30 AM
    Friday, October 22, 2010 5:35 PM
  • I've always done something like this:

    Dim rng As Range, i As Integer, j As Integer
    Set rng = Sheet1.UsedRange
    For i = 1 to rng.Rows.Count
     For j = 1 to rng.Columns.Count
      If rng(i,j) = "textX" Then
       Debug.Print rng(i,j)
    '
    ' --- I'd delete the exit for
    '
    '   Exit For
    ' 
    ' --- here's my code entry
    '
       j=rng.columns.count
       i=rng.rows.count
    
      End If
     Next
    Next
    • Marked as answer by Bessie Zhao Monday, November 01, 2010 9:31 AM
    Friday, October 22, 2010 5:37 PM
  • I've always done something like this:

    Dim rng As Range, i As Integer, j As Integer
    
    Set rng = Sheet1.UsedRange
    
    For i = 1 to rng.Rows.Count
    
     For j = 1 to rng.Columns.Count
    
     If rng(i,j) = "textX" Then
    
      Debug.Print rng(i,j)
    
    '
    
    ' --- I'd delete the exit for
    
    '
    
    '  Exit For
    
    ' 
    
    ' --- here's my code entry
    
    '
    
      j=rng.columns.count
    
      i=rng.rows.count
    
    
    
     End If
    
     Next
    
    Next

    I thought about trying that.  I guess it is a way to break out of all For loops.  I will give that a try.
    Friday, October 22, 2010 6:47 PM