Using FREQUENCY in formula in VBA

[saltire1963]

Am trying to write a frequency formula in VBA that will look at Data in a dynamic range and calculate the frequency of the numbers 1 to 45 in this range. The data is in columns C to H down to rows FinalRow (whatever that happens to be).
The bins ie 1 to 45 are placed in Column X rows 2 to 46.
Therefore I would like to calculate the frequency of each number in the data range and place them in Column Y rows 2 to 46.

I have input the following formula:
Range("Y2:Y46").Select
Selection.FormulaArray = "=FREQUENCY(RC-22:R" & FinalRow & "C-17,RC-1:R46C-1)"
But the cells in Y2:Y46 remain blank. I’m new to this R1C1 type formula. Can anyone pls help?

 

[Andrew Poulsom]

Does this work for you?

Selection.FormulaArray = "=FREQUENCY(RC[-22]:R" & FinalRow & "C[-17],RC[-1]:R46C[-1])"

Note the square brackets around the relative references.

 

[saltire1963]

Works perfectly, thanks Andrew