Solved

VBA Excel 2000 - Array formula value to variable

Posted on 2011-02-18
2
365 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:csehz
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34925443
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
 
LVL 1

Author Closing Comment

by:csehz
ID: 34925460
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now