How to use worksheetFunction.Frequency

[Peter Janssens]

Hi,
I want to use the worksheetFunction.frequency function in VBA to generate a
frequency list of data.
I don't know how to return the result into the spreadsheet.Manually, I can
do it with an array function.

sub test()
set myBin = Range("A1:A10")
set myData= Range("C120")
?? = WorksheetFunction.Frequency(myData, myBinRange)
End sub

Any help?

Thanks.

 

[Norman Jones]

Hi Peter,

Try:

Sub test()
Dim arr()
Dim sh As Worksheet
Dim myBin As Range, myData As Range

Set sh = Sheets("Sheet1") '<<==== CHANGE
With sh
Set myBin = .Range("A1:A10")
Set myData = .Range("C120")

arr = WorksheetFunction.Frequency(myData, myBin)

.Range("F1").Resize(UBound(arr) - LBound(arr)) = arr
End With
End Sub

Change the sheet name and change F1 to suit.

 

[Peter Janssens]

Thanks, works like a breeze!

Hi Peter,

Try:

Sub test()
Dim arr()
Dim sh As Worksheet
Dim myBin As Range, myData As Range

Set sh = Sheets("Sheet1") '<<==== CHANGE
With sh
Set myBin = .Range("A1:A10")
Set myData = .Range("C120")

arr = WorksheetFunction.Frequency(myData, myBin)

.Range("F1").Resize(UBound(arr) - LBound(arr)) = arr
End With
End Sub

Change the sheet name and change F1 to suit.