• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to set the FormulaArray property of the Range Class

V

Villalobos

Active Member
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:

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!
 

Attachments

Your worksheet names don't need ' and they do need ! in the formula
Where you need "" use Chr(34) & Chr(34)
 
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?
 

Attachments

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.
 
You must log in or register to reply here.
Back
Top