frequency in VBA

[Dirk]

Dear all

at the end of this subroutine I am using the frequency function but I
need the {} around it in order to be calculated correctly. How can I do
this?

Sub histo()
Dim rg, rn As Range
Dim i, no As Integer
Dim rep1, rep2 As String
Dim max, min As Double

rep1 = InputBox("define the range of the variable")
Set rg = Range(rep1)

no = InputBox("insert the number of bins, otherwise leave empty")
'If no = Nothing Then no = rg.Rows.Count / 10

rep2 = InputBox("where should i put the bins")
Set rn = Range(rep2)

max = WorksheetFunction.max(rg)
min = WorksheetFunction.min(rg)
For i = 1 To no
rn.Cells(i, 1) = min + i * (max - min) / no
Next i
For i = 1 To no
rn.Cells(i, 2) = WorksheetFunction.Frequency(rg, rn.Cells(i, 1))
Next i

End Sub

 

[Excelibur]

When I recorded entering an array function with the macro recorder, i
got this result:


Code:
--------------------
Selection.FormulaArray = "=SUM(R[-3]C:R[-3]C[1]*R[-2]C:R[-2]C[1])"
--------------------


So for starters, i'd say you need to modify the line to
rn.Cells(i, 2).formulaArray = WorksheetFunction.Frequency(rg,
rn.Cells(i, 1))

If it doesn't work then, I don't know the answer, but I can look for
it. Just hola if you have a result.

 

[Kris]

For i = 1 To no
rn.Cells(i, 2) = WorksheetFunction.Frequency(rg, rn.Cells(i, 1))
Next i

frequency returns an array, not a single number. You can't use it that way.

There will be no loop.
I tried to describe range using "i" and "no", but you can simplify it.

i = 1
range (cells(i,2),cells(no,2)).formulaArray =
worksheetfunction.frequency (rg, range(cells(i,1),cells(no,1))