IF statement in VBA

[wrightyrx7]

HI all,

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

The formula i want to use is:

=IF(A3=A2,IF(B3=B2,G2+1,1),1)

my attempt at putting it into VBA is:

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

Please help

 

[circledchicken]

Hi,

Perhaps try it something like this:

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]

Try

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]

Some suggestions:

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

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]

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.

=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 :

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]

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]

Thanks Jonmo1 the array formula is now working.

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

=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]

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

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

This article from Colin Legg provides a detailed description of all your options:
Working With Range.FormulaArray In VBA | RAD Excel

 

[Jonmo1]

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]

PERFECT! Thank you so much