moving to next cell when using if statements

Question

I am very new using VBA. This is what I have done:

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

I am trying to have the program move to the next column with information and check if the "If statement" is satisfied. However, it does not do what it is supposed to after the "Else". Please help me!

Your question is not very clear. Which `If` do you refer to? Also, what do you mean by _it does not do what it is supposed to after the "Else"_? — byako, Jul 15 '15 at 19:59
You aren't really explaining much of what is supposed to happen after the else. What are you trying to accomplish after the else? and what is actually happening after the else? Also just as a side note, you have a lot of unnecessary things in your code. Like, you use `Sheet1.Activate` many times over. You only need to use that once, the first time. You also don't need the `Worksheets(1)` in front of all the `Range()` because `Worksheets(1)` refers to `Sheet1` which you have already activated. It is basically redundant. — Constuntine, Jul 15 '15 at 20:00
Thanks for your help. What I would like to have the program do is that if the statement 'If UserADT < ADT And UserSpeed < Speed Then' is not met, using info from a specific column, the program moves to the next column and check for ' If UserADT >= ADT And UserSpeed >= Speed Then' and so on until it finds the column that meets the requirements. — Lizmert Lopez, Jul 16 '15 at 15:49

score 1 · Answer 1 · edited May 23 '17 at 11:43

This may tidy your code up sufficiently to add some logic to the results being cycled through.

Sub tble() 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

The latter portion cycles though C9, G9, K9, O9 adding 4 to the column with each pass. You just need to check the values on each pass to see if they meet your criteria for a breakout.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

how come your first few entries (` .Range("C9, G9, K9, O9").Value = spnBridgeWidth.Value`) works? When I tried the same, it wouldn't let me? I tried `With Sheets(1) // .Range("C9").Value = spnBridgeWidth.Value` and get the RunTime 424 error. Is that because of how I am using "Sheets(1)" and his `Sheet1` is a variable that isn't "Sheets(1)"? — BruceWayne, Jul 15 '15 at 20:39
No, you can use the `Sheet1` Worksheet [.CodeName property](https://msdn.microsoft.com/en-us/library/office/ff837552.aspx), the Worksheet [.Name property](https://msdn.microsoft.com/en-us/library/office/ff196974.aspx) or the index on the [Worksheets collection](https://msdn.microsoft.com/en-us/library/office/aa224508(v=office.11).aspx). It was likely just a typo or possibly you were confusing Worksheet**s** with Worksheet. — , Jul 15 '15 at 21:04
Thanks for your help. What I would like to have the program do is that if the statement 'If UserADT < ADT And UserSpeed < Speed Then' is not met, using info from a specific column, the program moves to the next column and check for ' If UserADT >= ADT And UserSpeed >= Speed Then' and so on until it finds the column that meets the requirements. — Lizmert Lopez, Jul 16 '15 at 15:48

score 0 · Answer 2 · answered Jul 15 '15 at 20:36

Without some more information, the below could get you started. Note I removed the .Select and .Activate, as those aren't necessary and it makes the code a little easier to read:

Sub test()
Dim spnBridgeWidth, spnVenLnWidth, spnNoOfLn
Dim startCell     As Range, otherCell As Range

Set startCell = Range("C4")
Set otherCell = Range("G4")
spnBridgeWidth = 10
spnVenLnWidth = 20
spnNoOfLn = 40


With Sheet1
    .Range("C9").Value = spnBridgeWidth
    .Range("C10").Value = spnVehLnWidth
    .Range("C12").Value = spnNoOfLn

    .Range("G9").Value = spnBridgeWidth
    .Range("G10").Value = spnVehLnWidth
    .Range("G12").Value = spnNoOfLn

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

    .Range("O9").Value = spnBridgeWidth
    .Range("O10").Value = spnVehLnWidth
    .Range("O12").Value = spnNoOfLn
End With

With startCell
    ADT = .Value
    Speed = .Offset(1, 0).Value

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

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 = otherCell.Value
        Speed = otherCell.Offset(1, 0).Value

        SpaceAvailable = otherCell.Offset(10, 0).Value
        MinSpaceNMT = otherCell.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
End Sub

edit: Bah - Jeeped beat me to it, his looks a little more "put together" :P

moving to next cell when using if statements

2 Answers2