Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FormulaArray | Excel Worksheet Functions | |||
Converting to R1C1 in FormulaArray | Excel Programming | |||
FormulaArray..... HELP !!! | Excel Programming | |||
FormulaArray with existing cell | Excel Programming | |||
FormulaArray | Excel Programming |