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

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
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,685
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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

ADVERTISEMENT

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,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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top