excel - 使用 if 语句时移动到下一个单元格

Question

我是使用 VBA 的新手。这就是我所做的:

Dim ADT As String
    Dim Speed As String
    Dim SpaceAvailable As Integer
    Dim MinSpaceNMT As Integer
    Dim UserADT As Integer
    Dim UserSpeed  As Integer


    Sheet1.Activate
    Worksheets(1).Range("C9").Value = spnBridgeWidth.Value
    Worksheets(1).Range("C10").Value = spnVehLnWidth.Value
    Worksheets(1).Range("C12").Value = spnNoOfLn.Value

    Sheet1.Activate
    Worksheets(1).Range("G9").Value = spnBridgeWidth.Value
    Worksheets(1).Range("G10").Value = spnVehLnWidth.Value
    Worksheets(1).Range("G12").Value = spnNoOfLn.Value

    Sheet1.Activate
    Worksheets(1).Range("K9").Value = spnBridgeWidth.Value
    Worksheets(1).Range("K10").Value = spnVehLnWidth.Value
    Worksheets(1).Range("K12").Value = spnNoOfLn.Value

    Sheet1.Activate
    Worksheets(1).Range("O9").Value = spnBridgeWidth.Value
    Worksheets(1).Range("O10").Value = spnVehLnWidth.Value
    Worksheets(1).Range("O12").Value = spnNoOfLn.Value

    Sheet1.Activate
    Range("C4").Select

    ADT = ActiveCell.Value
    Speed = ActiveCell.Offset(1, 0).Value

    SpaceAvailable = ActiveCell.Offset(10, 0).Value
    MinSpaceNMT = ActiveCell.Offset(14, 0).Value

    UserADT = UserTool.txtADT.Value
    UserSpeed = UserTool.txtSpeed.Value


    If UserADT < ADT And UserSpeed < Speed Then

    If SpaceAvailable > MinSpaceNMT Then


   MsgBox "Safe passage can be within the bridge with Alt. 1"

   Else
            Sheet1.Activate
            Range("G4").Select

            ADT = ActiveCell.Value
            Speed = ActiveCell.Offset(1, 0).Value

            SpaceAvailable = ActiveCell.Offset(10, 0).Value
            MinSpaceNMT = ActiveCell.Offset(14, 0).Value

            UserADT = UserTool.txtADT.Value
            UserSpeed = UserTool.txtSpeed.Value


   If UserADT >= ADT And UserSpeed >= Speed Then

   If SpaceAvailable > MinSpaceNMT Then


            MsgBox "Safe passage can be within the bridge with Alt. 1.2"

   Else

            MsgBox "Safe passage can be outside the bridge"


              End If
           End If
       End If
   End If

我试图让程序移动到包含信息的下一列，并检查是否满足“If 语句”。但是，它并没有在“Else”之后做它应该做的事情。请帮我!

Answer 1

这可能会充分整理您的代码，以便为循环通过的结果添加一些逻辑。

子表()
Dim co As Long

    With Sheet1
        .Range("C9, G9, K9, O9").Value = spnBridgeWidth.Value
        .Range("C10, G10, K10, O10").Value = spnVehLnWidth.Value
        .Range("C12, G12, K12, O12").Value = spnNoOfLn.Value

        UserADT = UserTool.txtADT.Value
        UserSpeed = UserTool.txtSpeed.Value

        For co = 0 To 12 Step 4
            With .Range("C4").Offset(0, co)
                ADT = .Value
                Speed = .Offset(1, 0).Value 'could also be .Range("C5")
                SpaceAvailable = .Offset(10, 0).Value
                MinSpaceNMT = .Offset(14, 0).Value
            End With

            'At this point I am unclear on why the logic changes
            'from less than to greater than or equal

            'If UserADT < ADT And UserSpeed < Speed Then
            '    If SpaceAvailable > MinSpaceNMT Then

            'If UserADT >= ADT And UserSpeed >= Speed Then
            '    If SpaceAvailable > MinSpaceNMT Then
            '        MsgBox "Safe passage can be within the bridge with Alt. 1.2"
            '    Else
            '        MsgBox "Safe passage can be outside the bridge"
            'When you have cycled through and met your conditions, use:
            Exit For
            'and you will jump out of the loop
        Next co
    End With
End Sub

后一部分循环通过 C9、G9、K9、O9，每次通过时向列添加 4。您只需要检查每次传递的值，看看它们是否符合您的突破标准。

见 How to avoid using Select in Excel VBA macros了解更多摆脱依赖选择和激活来实现目标的方法。