Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Excel 2000 - Array formula value to variable

Posted on 2011-02-18
2
Medium Priority
?
418 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

886 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