Unable to set the FormulaArray property of the Range Class

[Villalobos]

Hello,

I would need a bit help to set FormulaArray property. In my sample file (on worksheet Evaluation, column H9:H until lastrow) I would like to count the unique "Internal code" per material but I receive Run-time error 1004, unable to set the FormulaArray property of the Range Class.

Here stop the code:

 .Range("$H9:$H" & OutputLastRow2).FormulaArray = "=SUM(IF(FREQUENCY(IF('" & SourceSheet3.Name & "'$E$9:$E$" & SourceLastRow3 & " =B9,IF('" & SourceSheet3.Name & "'$M$9:$M$" & SourceLastRow3 & "<>"",MATCH(""&'" & SourceSheet3.Name & "'$M$9:$M$" & SourceLastRow3 & ",'" & SourceSheet3.Name & "'$M$9:$M$" & SourceLastRow3 & "&"",0))),ROW('" & SourceSheet3.Name & "'$M$9:$M$" & SourceLastRow3 & ")-ROW('" & SourceSheet3.Name & "'$M$9" & SourceLastRow3 & ")+1),1))"

Could somebody help me to find that where I made mistake?

I attached the sample file.


Thanks in advance!

 

[Hui]

Your worksheet names don't need ' and they do need ! in the formula
Where you need "" use Chr(34) & Chr(34)

 

[Villalobos]

Hello Hui,

Could you please provide me a sample code because I did the modification as you wrote but something is not working well.

Could you see the file please?

 

[Deepak]

Check this..

 

[Villalobos]

Hi Deepak,

Thank you for your time.
And how do you extend the code until the lastrow of column B?

If I use this .Range("$H9:$H" & OutputLastRow2).FormulaArray = _ <-- doesn't work.

 

[Hui]

Got it,
Pls See attached
I ended up using R1C1 References instead of normal A1 references

 

[Deepak]

Hi,

Array formula doesn't work this way

.Range("$H9:$H" & OutputLastRow2).FormulaArray

Workaround is attached herewith.

 

[Villalobos]

Hui, Deepak please receive my big thank you for the help!

 

[Deepak]

successfully received.