Q
QTE
Hi Excel Forum,
I'm using Excel's Frequency function in a VBA.
The Worksheet Syntax:
FREQUENCY(data_array, bins_array)
It must be must be entered as an array formula.
This is what I've got working so far in the macro VBA:
Range("R4C6:R17C6").FormulaArray = "=Frequency(R4C3:R34C3,R4C5:R17C5)
--- This works o.k: so far, so good.
However, I need the above VBA formula to be flexible enough to tak
into account that, the last cell in the range at the moment (R34C3
last part of the Frequency data_array syntax) will vary and I want t
avoid manually editing the VBA code each time it changes.
I've tried to expand on the above working Formula Array that uses th
fixed range of R4C3:R34C3, omitting the R34C3.
Range("R4C6:R17C6").Value = Application.Frequency(Range("R4C3
R4C5:R17C5", ActiveSheet.Range("R4C3").End(xlDown))).Row
As you can see: I've made a hash of it! Error: Run-time error 1004
Range method of Worksheet class failed.
Can you provide a VBA Formula that will work if I only have the star
cell range, i.e. R4C3.
Please advise and assist with a working example.
Thank you
QT
I'm using Excel's Frequency function in a VBA.
The Worksheet Syntax:
FREQUENCY(data_array, bins_array)
It must be must be entered as an array formula.
This is what I've got working so far in the macro VBA:
Range("R4C6:R17C6").FormulaArray = "=Frequency(R4C3:R34C3,R4C5:R17C5)
--- This works o.k: so far, so good.
However, I need the above VBA formula to be flexible enough to tak
into account that, the last cell in the range at the moment (R34C3
last part of the Frequency data_array syntax) will vary and I want t
avoid manually editing the VBA code each time it changes.
I've tried to expand on the above working Formula Array that uses th
fixed range of R4C3:R34C3, omitting the R34C3.
Range("R4C6:R17C6").Value = Application.Frequency(Range("R4C3
R4C5:R17C5", ActiveSheet.Range("R4C3").End(xlDown))).Row
As you can see: I've made a hash of it! Error: Run-time error 1004
Range method of Worksheet class failed.
Can you provide a VBA Formula that will work if I only have the star
cell range, i.e. R4C3.
Please advise and assist with a working example.
Thank you
QT