CODEWITHSUNDEEP
vbaexcel
Dan S
Dan S

Reputation: 147

Add a New If Statement within an Existing If Block

I have this code that compares Columns A and B and adds 1 to column B if A is greater:

Sub test07()

    With Sheets("Sheet1")

        Dim LastRow As Long, i As Long

        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

        For i = 12 To LastRow

            If Range("A" & i).Value > Range("B" & i).Value Then

                Range("B" & i).Value = Range("B" & i).Value + 1

            End If

        Next i

    End With

End Sub

I would like to add the same thing again but with columns C and D, but I am getting syntax errors, ie:

Sub test07()

    With Sheets("Sheet1")

        Dim LastRow As Long, i As Long

        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

        For i = 12 To LastRow

            If Range("A" & i).Value > Range("B" & i).Value Then

                Range("B" & i).Value = Range("B" & i).Value + 1

            If Range("C" & i).Value > Range("D" & i).Value Then

                Range("D" & i).Value = Range("D" & i).Value + 1

            End If

        Next i

    End With

End Sub

Can anyone see where I am going wrong? Many thanks

Upvotes: 3

Views: 78

Answers (3)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

You should also become familiar with ElseIf - ElseIf is the same as doing an End If followed by a new If statement except that it only runs if the first If statement resulted in False. ie: assume A1 = 5, and you want to check A1's value to determine B1's value. You could check values in two ways:

Option 1, using End If followed by a new If statement:

If Range("A1") > 3 Then 
    B1 = 2
End If
If Range("A1") > 4 Then 
    B1 = 1
End If 'Because A1 = 5, both If statements are True, and therefore B1 will equal 1, because that is the last line of code which affects it

Option 2, using ElseIf:

If Range("A1") > 3 Then 
    B1 = 2
ElseIf Range("A1") > 4 Then 
    B1 = 1
End If 'Because A1 = 5, the first If statement is True, the ElseIf statement never runs, and therefore B1 will equal 2, because that is the only line of code which affects it.

Both methods are valid - you just need to understand the logic pathways that you are actually after to decide which you want to use.

Upvotes: 0

grug.0
grug.0

Reputation: 355

you can simply also copy/paste your code and modify the 

Range("A" & i) --> Range("A" & i).Offset(#rows,#cols)

Or even better yet, ditch the "Range" and use "Cells" with two iterators and a nested FOR loop...

With...

for i in {#rowStart} To {#rowEnd}
    for j in {#colstart} To {#Colend}
        .Cells(i,j).Value = {put stuff here}

use the Ubound(Range()) to count elements in an array and size up your i & j, etc....

Upvotes: 1

user4039065
user4039065

Reputation:

As mentioned in the comments, you are missing an End If. However, you are also not taking full advantage of the explicit parentage that comes with using the With ... End With statement to identify the worksheet.

Sub test07()
    Dim lastRow As Long, i As Long
    With Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 12 To lastRow
            If .Range("A" & i).Value > .Range("B" & i).Value Then
                .Range("B" & i).Value = .Range("B" & i).Value + 1
            End If   '<~~might have to be three lines down depending upon how you want your logic to flow
            If .Range("C" & i).Value > .Range("D" & i).Value Then
                .Range("D" & i).Value = .Range("D" & i).Value + 1
            End If
        Next i
    End With
End Sub

Note the use of .Range and .Cells ; not Range and Cells. The prefix period (aka full stop) associates the ranges and cells with the worksheet referenced in the With ... End With.

Getting back to the If ... End If issue, if you wish to avoid closing off the If statements, you can use them like the following.

Sub test07()
    Dim lastRow As Long, i As Long
    With Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 12 To lastRow
            If .Range("A" & i).Value > .Range("B" & i).Value Then _
                .Range("B" & i).Value = .Range("B" & i).Value + 1
            If .Range("C" & i).Value > .Range("D" & i).Value Then _
                .Range("D" & i).Value = .Range("D" & i).Value + 1
        Next i
    End With
End Sub

This method only works with a single relevant code line coming after the If code line.

Upvotes: 5

Related Questions