# IF statement in VBA

#### wrightyrx7

##### Well-known Member
HI all,

I have the following code but do not know how to put the formula in:-

The formula i want to use is:
Code:
``=IF(A3=A2,IF(B3=B2,G2+1,1),1)``

my attempt at putting it into VBA is:
Code:
``````Sub test()    Dim lastRow As Integer
Dim rngSeq As Range
Dim i As Range

lastRow = Range("A3").End(xlDown).Row

With ActiveSheet
Set rngSeq = Range("M3:M" & lastRow)

For Each i In rngSeq.Cells
i.Formula = "=if(A" & .Row = A" & .Row - 1,

Next i
End With

End Sub``````

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### circledchicken

##### Well-known Member
Hi,

Perhaps try it something like this:

Code:
``````Sub test()

Dim lastRow As Long

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("M3:M" & lastRow).Formula = _
"=IF(A3 = A2, IF(B3 = B2, G2 + 1, 1), 1)"
End With

End Sub``````

#### Jonmo1

##### MrExcel MVP
Try
Code:
``````Sub test()
Dim lastRow As Long, rngSeq As Range
lastRow = Range("A3").End(xlDown).Row
Set rngSeq = Range("M3:M" & lastRow)
rngSeq.Formula = "=IF(A3=A2,IF(B3=B2,G2+1,1),1)"
End Sub``````

#### JackDanIce

##### Well-known Member
Some suggestions:
Code:
``````Sub test2()
Dim lRow As Long, i As Long
Application.ScreenUpdating = False
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).row
For i = 3 To lRow
.Range("M" & i).Formula = "=IF(A" & i & "=A" & i - 1 & ",IF(B" & i & "=B" & i - 1 & ",G" & i & "+1,1),1)"
Next i
End With
Application.ScreenUpdating = True
End Sub``````
And
Code:
``````Sub test3()
Dim lRow As Long

Application.ScreenUpdating = False

With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).row
.Range("M3").Formula = "=IF(AND(A3=A2,B3=B2),G2+1,1)"
.Range("M3:M" & lRow).FillDown
End With

Application.ScreenUpdating = True

End Sub``````

#### wrightyrx7

##### Well-known Member

Fantastic!! thank you both.

Tried them both for feedback purposes and both work great. Thank you again.

Can any of you help to input the following FormulaArray into column "N" in the same way as the previous.

Code:
``=INDEX(Allowances!D\$1:D\$198,MATCH(1,(Allowances!C\$1:C\$198=G3)*(Allowances!E\$2:E\$198=B3),0))``

I tried to input :

Code:
``````Sub test()
Dim lastRow As Long

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("N3:N" & lastRow).FormulaArray = _
"=INDEX(Allowances!D\$1:D\$198,MATCH(1,(Allowances!C\$1:C\$198=G2)*(Allowances!E\$2:E\$198=B3),0))"
End With

End Sub``````

But the G2 and B3 remain static and do not change.

Regards
Chris

#### Jonmo1

##### MrExcel MVP
For entering ARRAY formula in VBA, you have to follow Jacks 2nd example.
Enter the array formula in the first cell only, then fill down.

#### wrightyrx7

##### Well-known Member

Thanks Jonmo1 the array formula is now working.

Now for one last one if you can help because i keep getting an error message

Code:
``=IF(I3="N","",IFERROR(VLOOKUP(A3,'Active TP'!\$A\$2:\$E\$1976,5,FALSE),""))``

I think when i put it into VBA i need some more "" in it, but not 100%

Thanks again

#### circledchicken

##### Well-known Member
In addition to Jacks 2nd example, there are a few other methods you can try. For example:

Code:
``````Sub test()

Dim lastRow As Long

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("N3:N" & lastRow)
.Formula = "=INDEX(Allowances!D\$1:D\$198,MATCH(1,(Allowances!C\$1:C\$198=G2)*(Allowances!E\$2:E\$198=B3),0))"
.FormulaArray = .FormulaR1C1
End With
End With

End Sub``````
Working With Range.FormulaArray In VBA | RAD Excel

#### Jonmo1

##### MrExcel MVP
Yep, when entering a formula through VBA, and that formula contains quote marks, you have to double up the quote marks.

Try
=IF(I3=""N"","""",IFERROR(VLOOKUP(A3,'Active TP'!\$A\$2:\$E\$1976,5,FALSE),""""))

#### wrightyrx7

##### Well-known Member
PERFECT! Thank you so much

Replies
2
Views
55
Replies
7
Views
61
Replies
4
Views
30
Replies
2
Views
35
Replies
5
Views
53

1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...