Frequency FormulaArray Syntax

[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 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 star
cell range, i.e. R4C3.

Please advise and assist with a working example.

Thank you
QT

 

[Juan Sanchez]

QTE

'==========================================
Sub DefineRange()

Dim MyRange As String
Dim MyFormula As String
Dim i As Integer, j As Integer

Cells(4, 3).Select
Range(Selection, Selection.End(xlDown)).Select

MyRange = Selection.Address(ReferenceStyle:=xlR1C1)

For i = 1 To Len(MyRange)
If Mid(MyRange, i, 1) = ":" Then j = i
Next

MyRange = Mid(MyRange, j + 1, Len(MyRange))
MyFormula = "=Frequency(R4C3:" & MyRange & ",R4C5:R17C5)"
Range("F4:F17").FormulaArray = MyFormula
End Sub
'=================================================

<<<<<<<<<<< UNTESTED >>>>>>>>>>>>>>>>>>

Cheers
Juan

 

[QTE]

Hi Juan,

It works: thank you very much for your help. Greatly appreciated.

Juan, if possible could you explain to a novice:

'-----------------------------------------------------------------------------
I understand the selection of rows and the assignment of MyRange.

Cells(4, 3).Select
Range(Selection, Selection.End(xlDown)).Select
MyRange = Selection.Address(ReferenceStyle:=xlR1C1)
'------------------------------------------------------------------------------
This is where I become unstuck!

For i = 1 To Len(MyRange)
If Mid(MyRange, i, 1) = ":" Then j = i
Next

MyRange = Mid(MyRange, j + 1, Len(MyRange))
MyFormula = "=Frequency(R4C3:" & MyRange & ",R4C5:R17C5)"

We know the cell reference start point is R4C3 and it has a colo
character after it, that makes it R4C3: in the Myformula assignment.

We know the bins_array range R4C5:R17C5, that makes it R4C5:R17C5 i
the Myformula assignment.

Is MyRange counting and exrtracting number of characters from withi
the text string by using LEN and MID function to take account of th
unknown End or Last cell in the range (R4C3:?) made by the earlie
Cells and Range Selection?

Is the line below saying = ":" (equal to the colon)?:

If Mid(MyRange, i, 1) = ":" Then j = i

Is it counting number of characters after the colon up to and
including the variable MyRange?
'-----------------------------------------------------------------------------------
If you could explain what these lines are doing and how it works:

For i = 1 To Len(MyRange)
If Mid(MyRange, i, 1) = ":" Then j = i
Next
MyRange = Mid(MyRange, j + 1, Len(MyRange))
MyFormula = "=Frequency(R4C3:" & MyRange & ",R4C5:R17C5)"
'----------------------------------------------------------------------------------

Help, please.

Kind regards,
QTE

 

[Juan Sanchez]

QTE

For i = 1 To Len(MyRange)
If Mid(MyRange, i, 1) = ":" Then j = i
Next

This For cicle will evaluate each of the characters of the
MyRange String which is the addres to wich the selection
points after you do the Selection.End(xldown) thing.

once it finds a colon it will record the number of
character the colon is so that we can then extract the
last part, that may vary according to the ammount of rows
that you have.

MyRange = Mid(MyRange, j + 1, Len(MyRange))

Once the sub knows the number of character the colon is
(the one that is now recorded on j) it extracts the final
cell from the addres (j+1 so it avoids returning the colon)

MyFormula = "=Frequency(R4C3:" & MyRange & ",R4C5:R17C5)"

Finally, it crates the formula and stores it in MyFormula,
so that it can be used on the "object.FormulaArray =
Formula as string" part.

I did it that way to show how you can extract a part of a
string based on one character, but you could avoid all
that string managment by using this:

Sub DefineRange()

Dim MyRange As String
Dim MyFormula As String

Cells(4, 3).Select
Range(Selection, Selection.End(xlDown)).Select
MyRange = Selection.Address(ReferenceStyle:=xlR1C1)
MyFormula = "=Frequency(" & MyRange & ",R4C5:R17C5)"
Range("F4:F17").FormulaArray = MyFormula

End Sub

Cheers
Juan

 

[Juan Sanchez]

QTE

Further more...

Sub DefineRange()
Range("F4:F17").FormulaArray = "=Frequency(" & Range(Cells
(4, 3), Cells(4, 3).End(xlDown)).Address
(ReferenceStyle:=xlR1C1) & ",R4C5:R17C5)"
End Sub

Cheers
Juan

 

[QTE]

Hi Juan,

I really do appreciate you taking the time to explain the procedure an
syntax involved, including the alternative methods. It really ha
helped a novice, novice like me!

I can't thank you enough and everyone else who has helped me.

Thank you for taking the time and effort.

Kind regards
QT

 

[Juan Sanchez]

QTE, I get alot from the NGs so the least I can do is give
something back... pay it forward!!!

I love it when people take the time to thank back after
the end of a threath... Thank you for that...

Cheers
Juan