VBA .Replace in a FormulaArray

Question

I have written a rather long formula, so I had to cut it part. 

However, this doesn't work as my answer is "#NAME?" in excel, and the formula is : "=IF(SUM((WWW))=0,""-"",SUM((WWW)))"

What am I doing wrong? The formula works if remove the "=" in it, and put this in place manually in excel.

    Dim theFormulaPart1 As String

    Dim theFormulaPart2 As String

    Dim theFormulaPart3 As String

    Dim theFormulaPart4 As String

    Dim theFormulaPart5 As String

    theFormulaPart1 = "=IF(SUM((WWW))=0,""-"",SUM((WWW)))"

    theFormulaPart2 = "IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""EAZA""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+XXX"

    theFormulaPart3 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Temporary""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+YYY"

    theFormulaPart4 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Associate""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+ZZZ"

    theFormulaPart5 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""N-EEP""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1)"

     With ActiveSheet.Range("I42")

        .FormulaArray = theFormulaPart1

        .Replace "WWW", theFormulaPart2, xlPart

        .Replace "XXX", theFormulaPart3, xlPart

        .Replace "YYY", theFormulaPart4, xlPart

        .Replace "ZZZ", theFormulaPart5, xlPart

    End With

Furthermore, the set-up seems to work fine:

    Dim theFormulaPart1 As String

    Dim theFormulaPart2 As String

    Dim theFormulaPart3 As String

    Dim theFormulaPart4 As String

    Dim theFormulaPart5 As String

    theFormulaPart1 = "=IF(SUM(WWW)=0,""-"",SUM((WWW)))"

    theFormulaPart2 = "3+XXX"

    theFormulaPart3 = "4+YYY"

    theFormulaPart4 = "6+ZZZ"

    theFormulaPart5 = "7"

     With ActiveSheet.Range("I36")

        .FormulaArray = theFormulaPart1

        .Replace "WWW", theFormulaPart2, xlPart

        .Replace "XXX", theFormulaPart3, xlPart

        .Replace "YYY", theFormulaPart4, xlPart

        .Replace "ZZZ", theFormulaPart5, xlPart

    End With

Answer 1

You may. This is a site that explains my formula and the different components:

https://exceljet.net/formula/count-unique-text-values-in-a-range

Answer 2

Hi.   May I ask:  What does the following do?

MATCH( MMC, MMC, 0 )

Answer 3

I have tested all the separate parts of the formula, and they work.

I've also tested the VBA, by assembling the formula, but leaving out the "=". If I then manually add the "=" in the Excel sheet (and change the RC notation) it works perfectly. So the formulas seem to be correct.

However, I encounter issues as soon as I want to let the VBA assemble the formula as a formula (so if I add the "=").

So, for now I broke up the formula as follows, which works fine:

In Excel:

I3: =IF(SUM(J3:M3)=0;"-";SUM(J3:M3))

J3: =SUM(IF(FREQUENCY(IF((SN=A3)*(Total>0)*(MBR="EAZA");MATCH(MMC;MMC;0));MATCH(MMC;MMC;0))>0;1))

K3: =SUM(IF(FREQUENCY(IF((SN=A3)*(Total>0)*(MBR="Temporary");MATCH(MMC;MMC;0));MATCH(MMC;MMC;0))>0;1))

L3: =SUM(IF(FREQUENCY(IF((SN=A3)*(Total>0)*(MBR="Associate");MATCH(MMC;MMC;0));MATCH(MMC;MMC;0))>0;1))

M3: =SUM(IF(FREQUENCY(IF((SN=A3)*(Total>0)*(MBR="N-EEP");MATCH(MMC;MMC;0));MATCH(MMC;MMC;0))>0;1))

And this is what it looks like in the VBA:

    Range("I3").Select

    ActiveCell.FormulaR1C1 = "=IF(SUM(RC[1]:RC[4])=0,""-"",SUM(RC[1]:RC[4]))"

    Range("J3").Select

    Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF((SN=RC[-9])*(Total>0)*(MBR=""EAZA""),MATCH(MMC,MMC,0)),MATCH(MMC,MMC,0))>0,1))"

    Range("K3").Select

    Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF((SN=RC[-10])*(Total>0)*(MBR=""Temporary""),MATCH(MMC,MMC,0)),MATCH(MMC,MMC,0))>0,1))"

    Range("L3").Select

    Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF((SN=RC[-11])*(Total>0)*(MBR=""Associate""),MATCH(MMC,MMC,0)),MATCH(MMC,MMC,0))>0,1))"

    Range("M3").Select

    Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF((SN=RC[-12])*(Total>0)*(MBR=""N-EEP""),MATCH(MMC,MMC,0)),MATCH(MMC,MMC,0))>0,1))"