MACRO won't use CSE Formula (Array Formula)

[johnnyb5]

I use 7 CSE Formulas on a large spreadsheet (one at a time though). When I type them in manually they work fine. However, they are very long so I wanted a MACRO to type them for me. When I try to record this with a MACRO it won't work. Does anyone know if CSE formulas can be used with MACROs or VBA code.

My CSE Formula looks like this:
Range("C2").Select
Selection.FormulaArray = _
"=IF(COUNTIF($A$2:$A2,$A2)=COUNTIF(LIN,$A2), TRIM(ACONCAT(IF(FREQUENCY(IF(EIC<>"",IF(LIN=$A2, MATCH(EIC,EIC,0))),IVEC),""&EIC,""))),"")"

The MACRO changes the cell names to R1C1 format and the code won't appear in the cell (C2) when I stop recording and try to use it. Is there a proper format for using CSE formulas in VBA???

 

[MARK858]

First of all the R1C1 code below works fine for me from the recorder

    Range("C2").FormulaArray = _
        "=IF(COUNTIF(R2C1:R[-12]C1,R[-12]C1)=COUNTIF(LIN,R[-12]C1), TRIM(ACONCAT(IF(FREQUENCY(IF(EIC<>"""",IF(LIN=R[-12]C1, MATCH(EIC,EIC,0))),IVEC),""""&EIC,""""))),"""")"

As for why your code isn't working it is probably just that you haven't doubled your quotes (see how they appear in the R1C1 FORMULA).

I haven't double checked it but try

Range("C2").FormulaArray = _
 "=IF(COUNTIF($A$2:$A2,$A2)=COUNTIF(LIN,$A2), TRIM(ACONCAT(IF(FREQUENCY(IF(EIC<>"""",IF(LIN=$A2, MATCH(EIC,EIC,0))),IVEC),""""&EIC,""""))),"""")"

 

[johnnyb5]

First of all the R1C1 code below works fine for me from the recorder

    Range("C2").FormulaArray = _
        "=IF(COUNTIF(R2C1:R[-12]C1,R[-12]C1)=COUNTIF(LIN,R[-12]C1), TRIM(ACONCAT(IF(FREQUENCY(IF(EIC<>"""",IF(LIN=R[-12]C1, MATCH(EIC,EIC,0))),IVEC),""""&EIC,""""))),"""")"

As for why your code isn't working it is probably just that you haven't doubled your quotes (see how they appear in the R1C1 FORMULA).

I haven't double checked it but try

Range("C2").FormulaArray = _
 "=IF(COUNTIF($A$2:$A2,$A2)=COUNTIF(LIN,$A2), TRIM(ACONCAT(IF(FREQUENCY(IF(EIC<>"""",IF(LIN=$A2, MATCH(EIC,EIC,0))),IVEC),""""&EIC,""""))),"""")"

Yes Indeed!! My problem with the CSE formulas working inside a MACRO does in fact appear to be the double quotes. It did take a while to correct and run all seven of them but your astute eye and advice were correct.
Thanks Again Mark!
John

 

[MARK858]

Hi John, just to point out that the double quotes don't apply to just array (CSE) formula in VBA, it applies to all formula.
If you want quotes to appear in the formula then you need to double them else VBA thinks you want to put in an empty string.