Long Formula Array

[dellzy]

Hi,
I have another long formula array.


A1 notation style:
=IF($C2="New Record","",IFERROR(IF($H2<>"",$G2,IFERROR(IF($M2="","",IF(MAX(IF($A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))=$A2,$M3:INDEX($M3:$M$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))))=0,INDEX($G3:$G$17992,MATCH(1,("New Record"=$C3:$C$17992)*($A2=$A3:$A$17992),0)),MAX(IF($A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))=$A2,$M3:INDEX($M3:$M$17992,MATCH(9.99999999999999E+307,$M3:$M$17992)))))),$G2)),$G2))



I broke them down to become 4 parts :-

[COLOR=#141414][FONT=Verdana]    FPart1 = "=IF(RC3=""New Record"","""",IFERROR(IF(RC8<>"""",RC7,IFERROR(IF(RC13="""","""",IF(XXXXX,YYYYY,ZZZZZ)),RC7)),RC7))"[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]    FPart2 = "MAX(IF(R[1]C1:INDEX(R[1]C1:R5C1,MATCH(9.99999999999999E+307,R[1]C13:R5C13))=RC1,R[1]C13:INDEX(R[1]C13:R5C13,MATCH(9.99999999999999E+307,R[1]C13:R5C13))))=0"[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]    FPart3 = "INDEX(R[1]C7:R5C7,MATCH(1,(""New Record""=R[1]C3:R5C3)*(RC1=R[1]C1:R5C1),0))"[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]    FPart4 = "MAX(IF(R[1]C1:INDEX(R[1]C1:R5C1,MATCH(9.99999999999999E+307,R[1]C13:R5C13))=RC1,R[1]C13:INDEX(R[1]C13:R5C13,MATCH(9.99999999999999E+307,R[1]C13:R5C13))))))"[/FONT][/COLOR]

[COLOR=#141414][FONT=Verdana]Application.ReferenceStyle = xlR1C1[/FONT][/COLOR]

[COLOR=#141414][FONT=Verdana]With ActiveSheet.Range("L2")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].FormulaArray = FPart1[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Replace "XXXXX", FPart2, lookat:=xlPart[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Replace "YYYYY", FPart3, lookat:=xlPart[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Replace "ZZZZZ))))", FPart4, lookat:=xlPart[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.ReferenceStyle = xlA1[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Selection.AutoFill Destination:=Range("L2:L5")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Range("L2:L5").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Range("L2").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End Sub[/FONT][/COLOR]

When I run the macro, I have no error but in the cell, it came out like this:-
=IF(RC3="New Record","",IFERROR(IF(RC8<>"",RC7,IFERROR(IF(RC13="","",IF(MAX(IF($A3:INDEX($A3:$A$5,MATCH(9.99999999999999E+307,$M3:$M$5))=$A2,$M3:INDEX($M3:$M$5,MATCH(9.99999999999999E+307,$M3:$M$5))))=0,INDEX($G3:$G$5,MATCH(1,("New Record"=$C3:$C$5)*($A2=$A3:$A$5),0)),ZZZZZ)),RC7)),RC7))



I have tried to change the brackets, but all to no avail. All my trials will returned error.


Please help to correct my code.


Thanks in advance.
DZ

 

[dellzy]

I found why the first part of your formula is ending up wierd..

[COLOR=#141414]FPart1 = "=IF(RC3=""New Record"","""",IFERROR(IF(RC8<>"""",RC7,IFERROR(IF(RC13="""","""",IF(XXXXX,YYYYY,ZZZZZ)),RC7)),RC7))"[/COLOR]
....
[FONT=Verdana][COLOR=#141414].FormulaArray = FPart1[/COLOR][/FONT]

That formula by itself is VALID as either R1C1 style OR A1 Style.
.FormulaArray does not specify which style the input formula needs to be in, it will accept either.
Since it appears to be valid as A1 style, that's what it uses.
So it's reading RC3 as the 3rd row of column RC

Then the A1 style reference RC3 converted to R1C1 (because you set r1c1 stle for the application) is actually R3C471
It's READING your input formula 'FPart1' as A1 Style, but ENTERING it into the cell as R1C1 Style.


I was able to workaround that by changing this
FPart1 = "=IF(RC3=""New Record"","""",IFERROR(IF(RC8<>"""",RC7,IFERROR(IF(RC13="""","""",IF(XXXXX,YYYYY,ZZZZZ)),RC7)),RC7))"
to
FPart1 = "=IF(R[]C3=""New Record"","""",IFERROR(IF(R[]C8<>"""",R[]C7,IFERROR(IF(R[]C13="""","""",IF(XXXXX,YYYYY,ZZZZZ)),R[]C7)),R[]C7))"

Thereby forcing it to read it as R1C1Style.

Thank you so much, Jonmo1. From your findings on "repetitive formulas", I simplified my coding and got it all work out now. Thanks!

DZ

 

[Jonmo1]

Hye Jonmo1,

Sorry, I don't get what you mean. Maybe I rephrase my question.

Today, my range of data is $M3:$M$17992
Tomorrow it could be maybe just $M3:$M$100

Pick an sufficiently large number that you can reasonably say your data will never go further down than.
Use that number +10%

You might even just use the last row # of a sheet, 1048576

And create named ranges for ALL 4 ranges (A C G and M), and a seperate one for the last row#

Say
LRow : =MATCH(9.99999999999999E+307,$M$3:$M$1048576)
Arng : =$A$3:INDEX($A$3:$A$1048576,LRow)
Crng : =$C$3:INDEX($C$3:$C$1048576,LRow)
Grng : =$G$3:INDEX($G$4:$G1048576,LRow)
Mrng : =$M$3:INDEX($M3:$M$1048576,LRow)

Then you can use this without worrying about needing to break it up into smaller pieces.
=IF($C2="New Record","",IFERROR(IF($H2<>"",$G2,IFERROR(IF($M2="","",IF(MAX(IF(Arng=$A2,Mrng))=0,INDEX(Grng,MATCH(1,("New Record"=Crng)*($A2=Arng),0)),MAX(IF(Arng=$A2,Mrng)))),$G2)),$G2))

As VBA code

.FormulaArray = "=IF($C2=""New Record"","""",IFERROR(IF($H2<>"""",$G2,IFERROR(IF($M2="""","""",IF(MAX(IF(Arng=$A2,Mrng))=0,INDEX(Grng,MATCH(1,(""New Record""=Crng)*($A2=Arng),0)),MAX(IF(Arng=$A2,Mrng)))),$G2)),$G2))"

 

[Domenic]

I see that in the formula that you've assigned to FPart1 you're referring to "RC13". I guess it's recognizing it as an R1C1 reference since you have other R1C1 references that it already recognizes in the formula. Nevertheless, I would probably use "R[0]C13 just to be safe.