array formula with specific sheet reference

[macrorookie]

Hello -

I am trying my best to figure out what will probably be simple for most of you - hopefully.

My goal is to include an array formula into a macro, and I keep getting an error message. I am guessing that my problem is in the sheet reference (I really am a rookie), since I know my formula works outside of the macro (see cell E2 on the "Component Analysis" sheet on the attached sample file). Below is what I wrote to make the formula work in the marcro, but alas I'm missing something.

Note that the "general report" sheet data is irrelevant - only included to provide context to sheet names in the code below.

Sub count_test()
Worksheets("Component Analysis").Range("K3:K3").FormulaArray = "=SUM(IF(E4:I500<>"",1/COUNTIF(E4:I500,E4:I500)))"
End Sub

Thank you for any help!!

[AB33]

You need to wrap any string-including an empty string in to ""

Please Login or Register  to view this content.