IF statement in VBA

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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
Please help
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Feb 3, 2010
Messages
9,604
Office Version
365
Platform
Windows
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
Joined
Sep 15, 2011
Messages
994
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Sep 15, 2011
Messages
994
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
Joined
Aug 13, 2011
Messages
2,932
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
This article from Colin Legg provides a detailed description of all your options:
Working With Range.FormulaArray In VBA | RAD Excel
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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),""""))
 

Watch MrExcel Video

Forum statistics

Threads
1,096,297
Messages
5,449,518
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top