vba frequency function within a loop

[montecarlo2012]

Hi.
I am trying to loop the frequency function and I didn't get right
the code I write is:

Sub LO_FRE()

Dim i As Long, j As Long

         For i = 2 To 54
                  For j = 10 To 52
                  
                          Cells(i, j + 1).FormulaArray = "=frequency(R2C" & i & ":R54C" & i & ", R10C1:R52C1))"
                  
                  Next
         Next
End Sub

Is wrong, so this is the macro to illustrate what I am trying to do:

Sub Macro_1()
Range("J2:J54").FormulaArray = "=FREQUENCY(B2:G5,I2:I54)"
Range("K2:K54").FormulaArray = "=FREQUENCY(B2:G6,I2:I54)"
Range("L2:L54").FormulaArray = "=FREQUENCY(B2:G7,I2:I54)"
Range("M2:M54").FormulaArray = "=FREQUENCY(B2:G8,I2:I54)"
Range("N2:N54").FormulaArray = "=FREQUENCY(B2:G9,I2:I54)"

' and from here until ........................

Range("AZ2:AZ54").FormulaArray = "=FREQUENCY(B2:G46,I2:I54)"
End Sub

thanks.

 

[MUKESHY12390]

I would suggest record your action and then look into the syntax.

currently I don't have system, meanwhile you can try that.

 

[MUKESHY12390]

I would suggest record your action and then look into the syntax.

currently I don't have system, meanwhile you can try that.

okay, recorded one is already mentioned in post.

 

[offthelip]

try this simple change

Sub LO_FRE()

Dim i As Long, j As Long

         For i = 2 To 54
                  For j = 10 To 52
                  
                          Cells(i, j + 1).FormulaArray = "=frequency(B2:G" & j - 5 & ",I2:I54)"
                  
                  Next
         Next
End Sub

 

[montecarlo2012]

try this simple change

Sub LO_FRE()

Dim i As Long, j As Long

         For i = 2 To 54
                  For j = 10 To 52
                 
                          Cells(i, j + 1).FormulaArray = "=frequency(B2:G" & j - 5 & ",I2:I54)"
                 
                  Next
         Next
End Sub

thanks, but do not work, populate the area with the numbers from 1 to 6, that's it. and I have to add application.screenupdating, still do not work, but thank you, count for me you spent time trying, talk really nice about you.

 

[offthelip]

Are you sure you have got your Frequency equation correct. looking at it I think you have got the bin numbers and data the wrong way round. I believe my code does write out the equations you asked for
Also there is a much faster way of writing out the equations but I wanted to show you how to modify your code to get the equations you asked for, so you wouldn't need to turn screen updating off.

 

[montecarlo2012]

Thanks, Yes I am sure. also I did manually all trying to realize what change, what do not change and get somewhere but still.

Sub MSM()
Application.ScreenUpdating = False
Range("J2:J54").FormulaArray = "=FREQUENCY(B2:G5,I2:I54)"
Range("K2:K54").FormulaArray = "=FREQUENCY(B2:G6,I2:I54)"
Range("L2:L54").FormulaArray = "=FREQUENCY(B2:G7,I2:I54)"
Range("M2:M54").FormulaArray = "=FREQUENCY(B2:G8,I2:I54)"
Range("N2:N54").FormulaArray = "=FREQUENCY(B2:G9,I2:I54)"
Range("O2:O54").FormulaArray = "=FREQUENCY(B2:G10,I2:I54)"
Range("P2:P54").FormulaArray = "=FREQUENCY(B2:G11,I2:I54)"
Range("Q2:Q54").FormulaArray = "=FREQUENCY(B2:G12,I2:I54)"
Range("R2:R54").FormulaArray = "=FREQUENCY(B2:G13,I2:I54)"
Range("S2:S54").FormulaArray = "=FREQUENCY(B2:G14,I2:I54)"
Range("T2:T54").FormulaArray = "=FREQUENCY(B2:G15,I2:I54)"
Range("U2:U54").FormulaArray = "=FREQUENCY(B2:G16,I2:I54)"
Range("V2:V54").FormulaArray = "=FREQUENCY(B2:G17,I2:I54)"
Range("W2:W54").FormulaArray = "=FREQUENCY(B2:G18,I2:I54)"
Range("X2:X54").FormulaArray = "=FREQUENCY(B2:G19,I2:I54)"
Range("Y2:Y54").FormulaArray = "=FREQUENCY(B2:G20,I2:I54)"
Range("Z2:Z54").FormulaArray = "=FREQUENCY(B2:G21,I2:I54)"
Range("AA2:AA54").FormulaArray = "=FREQUENCY(B2:G22,I2:I54)"
Range("AB2:AB54").FormulaArray = "=FREQUENCY(B2:G23,I2:I54)"
Range("AC2:AC54").FormulaArray = "=FREQUENCY(B2:G24,I2:I54)"
Range("AD2:AD54").FormulaArray = "=FREQUENCY(B2:G25,I2:I54)"
Range("AE2:AE54").FormulaArray = "=FREQUENCY(B2:G26,I2:I54)"
Range("AF2:AF54").FormulaArray = "=FREQUENCY(B2:G27,I2:I54)"
Range("AG2:AG54").FormulaArray = "=FREQUENCY(B2:G28,I2:I54)"
Range("AH2:AH54").FormulaArray = "=FREQUENCY(B2:G29,I2:I54)"
Range("AI2:AI54").FormulaArray = "=FREQUENCY(B2:G30,I2:I54)"
Range("AJ2:AJ54").FormulaArray = "=FREQUENCY(B2:G31,I2:I54)"
Range("AK2:AK54").FormulaArray = "=FREQUENCY(B2:G32,I2:I54)"
Range("AL2:AL54").FormulaArray = "=FREQUENCY(B2:G33,I2:I54)"
Range("AM2:AM54").FormulaArray = "=FREQUENCY(B2:G34,I2:I54)"
Range("AN2:AN54").FormulaArray = "=FREQUENCY(B2:G35,I2:I54)"
Range("AO2:AO54").FormulaArray = "=FREQUENCY(B2:G36,I2:I54)"
Range("AP2:AP54").FormulaArray = "=FREQUENCY(B2:G37,I2:I54)"
Range("AQ2:AQ54").FormulaArray = "=FREQUENCY(B2:G18,I2:I54)"
Range("AR2:AR54").FormulaArray = "=FREQUENCY(B2:G39,I2:I54)"
Range("AS2:AS54").FormulaArray = "=FREQUENCY(B2:G40,I2:I54)"
Range("AT2:AT54").FormulaArray = "=FREQUENCY(B2:G41,I2:I54)"
Range("AU2:AU54").FormulaArray = "=FREQUENCY(B2:G42,I2:I54)"
Range("AV2:AV54").FormulaArray = "=FREQUENCY(B2:G43,I2:I54)"
Range("AW2:AW54").FormulaArray = "=FREQUENCY(B2:G44,I2:I54)"

Application.ScreenUpdating = True
End Sub

and also ( I am really working out)

Sub LO_FRE()

Dim i As Long, j As Long

         For i = 2 To 54
                  For j = 10 To 52
                  
                          'Cells(i + 2, j).FormulaArray = "=frequency(B2:G" & j - 5 & ",I2:I54)"
                'Cells(i, j + 1).FormulaArray = "=frequency(R2C" & i & ":R46C" & i & ", R10C1:R52C1))"
      'Cells(i, j + 1).FormulaArray = "=FREQUENCY(R2C" & i & ":R42C" & i & "45,R2C:R54C1])"
      Cells(i, j).FormulaR1C1 = "=frequency(R" & j + 1 & "C8:R" & j + 1 & "C54,RC1)"
                  Next
         Next
End Sub

I know is a mess but, I try first and ask for help after.