Range("Q28").FormulaArray = "=COUNTIF(M2:M2744,P28)"

[montecarlo2012]

Hello everyone.
What I am looking is expressed on this frequency function, and also I tried the formulaArray getting of course the same results, but what I really one is to do it with a Loop. (please).
{=FREQUENCY(M2:M2744,P28:P34)}

Sub VBA_COUN()
Range("Q28").FormulaArray = "=COUNTIF(M2:M2744,P28)"
Range("Q29").FormulaArray = "=COUNTIF(M2:M2744,P29)"
Range("Q30").FormulaArray = "=COUNTIF(M2:M2744,P30)"
Range("Q31").FormulaArray = "=COUNTIF(M2:M2744,P31)"
Range("Q32").FormulaArray = "=COUNTIF(M2:M2744,P32)"
Range("Q33").FormulaArray = "=COUNTIF(M2:M2744,P33)"
Range("Q34").FormulaArray = "=COUNTIF(M2:M2744,P34)"
End sub
The logic (wrong one) I tried was: on the left side the range go from 28 to 34, and on the right side (data source) the process is done from 2 to 2744, so the biggest number (or bigger repetition) go in the inner loop, reason why (out is: for j = 28 to 34) and in the second the array is dynamic so instead of, for I = 2 to 2744 I apply End(xlup).Row I want my result returned on “Q”.
But of course I am wrong in that logic, so please tell me the Stepwise refinement in order to get the right results.

Sub VBA_MREXCEL()
Dim I As Integer, J As Integer
For J = 28 To 34
For I = 2 To Cells(Rows.Count, "M").End(xlUp).Row
Range("Q", J + 27).Value = "=COUNTIF(i + 1 , j)"
Next I
Next J
End Sub

the results are something like this

Thanks for any possible help, and for reading this.

 

[StephenCrump]

'Perhaps
Range("Q28:Q34").Formula = "=COUNTIF(M$2:M$2744,P28)"

'Or
Range("Q28:Q34").Formula = "=COUNTIF(M$2:M$2744,ROWS(Q$28:Q28)-1)"

 

[montecarlo2012]

Thank you Mr. StephenCrump, both lines work perfect, so, if you don't mind, the logic step that I tried, I assume they are ok, or Do you have any coment, Please

 

[StephenCrump]

If you want to write cell by cell, you only need one loop, perhaps:

Dim i As Long, FirstRow As Long, LastRow As Long

FirstRow = 2
LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For i = 28 To 34
    Range("Q" & i).FormulaR1C1 = "=COUNTIF(R" & FirstRow & "C13:R" & LastRow & "C13,RC[-1])"
Next i

 

[montecarlo2012]

StephenCrump Thank you. I really appreciate your Help.