VBA Excel 2000 - Array formula value to variable

Dear Experts,

Could you please advise how to assign an array formula to a variable?

The attached small code works fine, but I would like if the NumPivotCat would store the formula result without writing it to any cell

thanks,


Sub CountCategories()
Dim NumPivotCat As Long
Worksheets("Sheet1").Range("C2").FormulaArray = "=SUM(IF(FREQUENCY(IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),""""), IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),""""))>0,1))+(COUNTIF(A1:A11,"""")>0)"
End Sub

Open in new window

LVL 1
csehzIT consultantAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Try:
Sub CountCategories()
Dim NumPivotCat As Long
NumPivotCat  = Evaluate("SUM(IF(FREQUENCY(IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),""""), IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),""""))>0,1))+(COUNTIF(A1:A11,"""")>0)")
End Sub

Open in new window

0
 
csehzIT consultantAuthor Commented:
I hoped that you will answer and can give the points. As the whole formula I got from you, thanks very much it works :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.