代码之家 › 专栏 › 技术社区 › stanigator

vba:跳出for循环

excel loops excel-vba vba

1

stanigator · 技术社区 · 16 年前

如何实现以下目标?

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    n = Cells(1, 1).End(xlDown).Row
    For i = 1 To n
        ActiveSheet.Cells(i, 2).Select
        *******************************************************
        If Worksheets("Misc").Cells(2, i).Value = "" Then
            continue i
        End If
        *******************************************************
        If Worksheets("Misc").Cells(3, i).Value <> "" Then
            Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
        Else
            Set validationRange = Worksheets("Misc").Cells(2, i)
        End If
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=validationRange.Address
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

3 回复 | 直到 16 年前

1

4

Mia Clarke 16 年前

这不是一个简单的流程控制案例吗?我不明白需要特殊的关键词来解决它:

For i = 0 To 10
 If Not condition Then 
    some other code
Next

编辑: 或者,在您的代码中:

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    n = Cells(1, 1).End(xlDown).Row
    For i = 1 To n
        ActiveSheet.Cells(i, 2).Select
        *******************************************************
        If Not Worksheets("Misc").Cells(2, i).Value = "" Then
        *******************************************************
          If Worksheets("Misc").Cells(3, i).Value <> "" Then
              Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
          Else
              Set validationRange = Worksheets("Misc").Cells(2, i)
          End If
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:=validationRange.Address
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
     ********
      End If
     ********
    Next 
End Sub

2

Tom Gruff 16 年前

回答另一半问题:

For n = 1 To something
    If condition Then
        Exit For
    End If
    ' more code

Next n

3

0

Eric 16 年前

vb的关键字不是大写的吗?

For n = 1 To something
    If condition Then
        Continue For
    End If

    ' more code

Next n

这个 Continue 关键字可以满足您的需要。

http://msdn.microsoft.com/en-us/library/801hyx6f(VS.80).aspx