# Using FREQUENCY in formula in VBA

#### saltire1963

##### Board Regular
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?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Andrew Poulsom

##### MrExcel MVP
Does this work for you?

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

Note the square brackets around the relative references.

#### saltire1963

##### Board Regular
Works perfectly, thanks Andrew

Replies
1
Views
162
Replies
4
Views
140
Replies
1
Views
126
Replies
4
Views
257
Replies
6
Views
530

1,191,383
Messages
5,986,305
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back