VBA Frequency FormulaArray Syntax
 

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

--
Message posted from http://www.ExcelForum.com

VBA Frequency FormulaArray Syntax
 

See answer in Worksheet Functinons... no need to post to
several groups...


-----Original Message-----
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 take
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 to
avoid manually editing the VBA code each time it changes.

I've tried to expand on the above working Formula Array
that uses the
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 start
cell range, i.e. R4C3.

Please advise and assist with a working example.

Thank you
QTE


---
Message posted from http://www.ExcelForum.com/

.