当某些条件为真时，退出“For Each”循环 [英] Exit 'For Each' loop when certain condition is true

问题描述

我需要在列X中的Sheet1上查看所有行，获取其值，然后查看值是否是存储在Sheet 2列A和B之间的BETWEEN数字组合。如果值在之间，则显示Sheet 2列中的值C在Sheet 1列Y（将是一个非常简单的SQL查询）。

我对VBA非常生锈，所以我不能退出内部循环，当有一个匹配，并粘贴从Sheet2到Sheet1的值。

  Sub FindBetweenIP（）
 
 Dim ws1 As工作表
设置ws1 =表格（1）
 
 Dim ws2 As Worksheet 
设置ws2 = Sheets（2）
 
对于每个单元格在ws1.Range （X2：X& ws1.Range（X& Rows.Count）.End（xlUp）.Row）
 
对于每个单元格2在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_ran ge1和cell.Value< = ip_range2）然后
 cell.Offset（0，1）.Value2 = isp'似乎不工作
'另外，VALUE FOUND  -  EXIT INNER LOOP 
结束如果
 
下一个
 
下一个
 End Sub 
  

解决方案

除了Eh培根对于退出对于的看法，你有一点断开在第二个循环范围：

  For each cell2在ws2.Range（A2：A& ws1.Range（A& Rows.Count）.End（xlUp）.Row）
  

你从ws2开始，但是你指的是ws1

更改为：

  For each cell2在ws2.Range（A2：A& ws2.Range（A& ws2.Rows.Count）.End（xlUp）.Row）
  

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

解决方案

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)

这篇关于当某些条件为真时，退出“For Each”循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！