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

 

[Domenic]

It looks like you may have too many brackets for the formula assigned to FPart4. Try...

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))))"

and

.Replace "ZZZZZ", FPart4, lookat:=xlPart

Hope this helps!

 

[dellzy]

Hi Domenic,

Thanks for the swift response. Your revised code has solved the FPart4. However, I still wonder why the smaller pieces of formulas in FPart1 still came out in R1C1 style after I run the macro?

Below the result in worksheet cell:-
=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)),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)))))),RC7)),RC7))

 

[Jonmo1]

I find with the 255 character limitation of array formulas in VBA, it's wise to explore options of just making the formula shorter..

For example, you have this setup repeated 4 times (twice indexing column A, and twice indexing column M)
$A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))
$M3:INDEX($M3:$M$17992,MATCH(9.99999999 999999E+307,$M3:$M$17992))

If you setup 2 named ranges for those

Say
Mrng : $A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))
Mrng2 : $M3:INDEX($M3:$M$17992,MATCH(9.99999999 999999E+307,$M3:$M$17992))

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(MRng=$A2,MRng2))=0,INDEX($G3:$G$17992,MATCH(1,("New Record"=$C3:$C$17992)*($A2=$A3:$A$17992),0)),MAX(IF(MRng=$A2,MRng2)))),$G2)),$G2))

 

[dellzy]

Hye Jonmo1,

Your solution seems smart and simple but I don't know how to make the range change dynamically? I mean, the data rows will change, depending how much data I'm merging (initial process will be merging rows of data from multiple workbooks). In my macro, I will actually adjust the code as something like "$M3:$M" & LRow where LRow is the total count of rows with data. But in my question here, I just put it as static range because I want to emphasise on the problem of having a long formulas in macro.

Any suggestion?

Thank you in advance.

DZ

 

[Jonmo1]

Those ranges will still auto adjust within the named range manager.
According to the row # found with this match
MATCH(9.99999999999999E+307,$M3:$M$17992)

 

[dellzy]

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

In VBA we can create a code to store the last of row data in a variable, eg. LRow

But how do I do this in named range?

Thanks in advance.
DZ

 

[Domenic]

However, I still wonder why the smaller pieces of formulas in FPart1 still came out in R1C1 style after I run the macro?

Actually, its recognizing references such as "RC3" as a cell address in the A1 reference style. So we'll have to ensure that Excel recognizes such references as an R1C1 reference. For example, "RC3" should be replaced with "R[0]C3". And, the same thing for the others.

 

[Jonmo1]

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.

 

[dellzy]

Hi Domenic,

Thank you for your answer. I tried changing the Column number to count backwards instead of forward. And I changed the formula part to be a bit simpler. So, I got it all correctly when I run the below:-

    FPart1 = "=IF(RC[-9]=""New Record"","""",IFERROR(IF(RC[-4]<>"""",RC[-5],IFERROR(IF(RC13="""","""",IF(XXXXX=0,YYYYY,XXXXX)),RC[-5])),RC[-5]))"
    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))))"
    FPart3 = "INDEX(R[1]C7:R5C7,MATCH(1,(""New Record""=R[1]C3:R5C3)*(RC1=R[1]C1:R5C1),0))"

Application.ReferenceStyle = xlR1C1
    
With ActiveSheet.Range("L2")
.FormulaArray = FPart1
.Replace "XXXXX", FPart2, lookat:=xlPart
.Replace "YYYYY", FPart3, lookat:=xlPart
End With


Application.ReferenceStyle = xlA1
Selection.AutoFill Destination:=Range("L2:L5")
Range("L2:L5").Select
Range("L2").Select

Again, thank you do much for your help.
DZ