Excel Daily News

How to use FREQUENCY function in VBA?

I have several thousand data values and I'd like to find out the distribution of these values. I'd like tohave about 100 values in the BIN range. Can anyoneshow me how to do this using the FREQUENCY functionvia VBA?

Thanks

Posted by Bob Umlas on November 06, 2001 7:49 AM

Assuming you have your value in the BIN range, say E1:E100, then FIRST SELECT F1:F100, and enter something like=FREQUENCY(A1:A4000,E1:E100) but be sure to use ctrl/shift/enter.This assumes your "several thousand data values" are in A1:A4000)

Posted by Ron Dionne on November 06, 2001 8:21 AM

Can I use VBA and arrays instead?

I recorded a macro to see the code produced:Selection.FormulaArray = "=FREQUENCY(RC[-2]:R[1239]C[-2],RC[-1]:R[20]C[-1])"

Is there a way to use the FREQUENCY function withoutresorting to the old R1C1 notation, and using VBA arrayainstead of the Ranges?

(8 responses) I have a column with either a value of 1 or blank. The sheet has a header. Is there code that can sort by value in column "Z", find the last

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.