I have the follwing VBA Code:
When I try to add additional formulas to the VBA Code the VBA throws out a error:
I want the VBA Code to place the following formulas in:
AB: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AA1,0)),1)),"")
AC: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AB1,0)),1)),"")
AD: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AC1,0)),1)),"")
and for them to run to the last entry in column AA
VBA Code:
'Create Formula in AB1:AD1
Dim LR As Long
LR = Cells(Rows.Count, "AA").End(xlUp).Row
With Range("AB1:AB" & LR)
.FORMULA = "=IFERROR(INDEX('Teller Stats'!C6,AGGREGATE(15,6,ROW('Teller Stats'!C6)/((('Teller Stats'!C9=Balance!RC27)+('Teller Stats'!C10=Balance!RC27))>0)/ISNA(MATCH('Teller Stats'!C6,Balance!RC27:RC[-1],0)),1)),"""")"
.NumberFormat = "0"
End With
End Sub
When I try to add additional formulas to the VBA Code the VBA throws out a error:
VBA Code:
'Create Formula in AB1:AD1
Dim LR As Long
LR = Cells(Rows.Count, "AA").End(xlUp).Row
With Range("AB1:AB" & LR)
.FORMULA = "=IFERROR(INDEX('Teller Stats'!C6,AGGREGATE(15,6,ROW('Teller Stats'!C6)/((('Teller Stats'!C9=Balance!RC27)+('Teller Stats'!C10=Balance!RC27))>0)/ISNA(MATCH('Teller Stats'!C6,Balance!RC27:RC[-1],0)),1)),"""")"
.NumberFormat = "0"
End With
LR = Cells(Rows.Count, "AA").End(xlUp).Row
With Range("AC1:AC" & LR)
.FORMULA = "=IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AB1,0)),1)),"")"
.NumberFormat = "0"
End With
End Sub
I want the VBA Code to place the following formulas in:
AB: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AA1,0)),1)),"")
AC: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AB1,0)),1)),"")
AD: =IFERROR(@INDEX('Teller Stats'!$F:$F,AGGREGATE(15,6,ROW('Teller Stats'!$F:$F)/((('Teller Stats'!$I:$I=Balance!$AA1)+('Teller Stats'!$J:$J=Balance!$AA1))>0)/ISNA(MATCH('Teller Stats'!$F:$F,Balance!$AA1:AC1,0)),1)),"")
and for them to run to the last entry in column AA