CODEWITHSUNDEEP
vbaexcel
Andrew
Andrew

Reputation: 7768

Exit 'For Each' loop when certain condition is true

I need to go over all rows on Sheet1 in Column X, grab its value and then, see if value is BETWEEN numbers combination stored on Sheet 2 columns A and B. If value is between, then show value from Sheet 2 Column C in the Sheet 1 Column Y (would be a very easy SQL query).

I am very rusty on VBA so I can't exit the inside loop when there is a match, and paste Value from Sheet2 to Sheet1.

Sub FindBetweenIP()

    Dim ws1 As Worksheet
    Set ws1 = Sheets(1)

    Dim ws2 As Worksheet
    Set ws2 = Sheets(2)

    For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)

        For Each cell2 In ws2.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)

            ip_range1 = cell2.Value2
            ip_range2 = cell2.Offset(0, 1).Value2
            isp = cell2.Offset(0, 2).Value2

            If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
                cell.Offset(0, 1).Value2 = isp 'Seems to be not working
                ' ALSO, VALUE FOUND-- EXIT INNER LOOP
            End If

        Next

    Next    
End Sub

Upvotes: 5

Views: 33214

Answers (4)

skkakkar
skkakkar

Reputation: 2828

With due respect to my seniors, I found that exit for approach is not synchronising the outer and inner loop properly in this case. I have worked out another approach which works properly and gives correct results. I stand to be corrected as may be my expertise in VBA is not to the level of my seniors and more experienced persons here.

  Sub FindBetweenIP()

     Dim ws1 As Worksheet
     Set ws1 = Sheets(1)

     Dim ws2 As Worksheet
     Set ws2 = Sheets(2)

     For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)

         Foundone = False
         For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
           If cell2.Row = cell.Row And Foundone = False Then
               ip_range1 = cell2.Value2
                ip_range2 = cell2.Offset(0, 1).Value2
                isp = cell2.Offset(0, 2).Value2
                If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
                 cell.Offset(0, 1).Value2 = isp
                 Foundone = True
               End If
           End If
     Next
     Next
 End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

In addition to what Grade 'Eh' Bacon said about the Exit For it appears you have a little disconnect on the second for loop range:

For Each cell2 In ws2.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)

you start with ws2 but inside you refer to ws1

Change it to:

For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row)

Upvotes: 4

Grade &#39;Eh&#39; Bacon
Grade &#39;Eh&#39; Bacon

Reputation: 3823

You are looking for the expression "Exit". In this case, it looks like so:

For i = 1 to 10

    [Do statements]
    [If Test]
        Exit For
    [End If]
 Next i

Exiting a loop in this way essentially works as though the code was jumped down to "Next i", with i already being equal to the maximum loop value.

Upvotes: 8

Kris B
Kris B

Reputation: 446

In VBA you should just use the 'goto' command. EG:

Sub FindBetweenIP()

    Dim ws1 As Worksheet
    Set ws1 = Sheets(1)

    Dim ws2 As Worksheet
    Set ws2 = Sheets(2)

    For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)

        For Each cell2 In ws2.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)

            ip_range1 = cell2.Value2
            ip_range2 = cell2.Offset(0, 1).Value2
            isp = cell2.Offset(0, 2).Value2

            If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
                cell.Offset(0, 1).Value2 = isp
                GoTo ExitInnerLoop:
            End If
        Next
ExitInnerLoop:
    Next    
End Sub

Upvotes: -2

Related Questions