vba. FormulaArray in a loop. "=FREQUENCY(Sheet2!R[2]C[9]:R[2]C[1460],Sheet1!RC[-3]:R[100]C[-3])"

[montecarlo2012]

Hello.

Sub Macro1()

    Range("B2:B102").Select
    Selection.FormulaArray = _
        "=FREQUENCY(Sheet2!RC[11]:RC[1528],Sheet1!RC[-1]:R[100]C[-1])"
    
End Sub
Sub Macro2()

    Range("C2:C102").Select
    Selection.FormulaArray = _
        "=FREQUENCY(Sheet2!R[1]C[10]:R[1]C[1482],Sheet1!RC[-2]:R[100]C[-2])"
   
End Sub
Sub Macro3()

    Range("D2:D102").Select
    Selection.FormulaArray = _
        "=FREQUENCY(Sheet2!R[2]C[9]:R[2]C[1460],Sheet1!RC[-3]:R[100]C[-3])"
   
End Sub

Thank you for reading this.
I don't know how to loop all these little macros.
my reading data are at sheet2 M2:M37 to the last row on each row, (each row is different)
my display data will be at sheet1 B2:B102
Have a nice Sunday.

 

[montecarlo2012]

Hi. I am still doing what I understand.
so.
Range("G2:G102").FormulaArray = "=FREQUENCY(Sheet2!M7:BMW7,Sheet1!A2:A101)"
if I repeat this line 37 time look like this:

then I putting some code like:

Sub wrong()

Dim I, j as variant
Dim LastColumn As Long

ActiveSheet.UsedRange
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For I = 2 to 102
          For j = 2 to LastColumn
                        Sheet1!Cells(I + 1,j + 1).formulaArray = “=frequency(sheet2! & j + 1 &”,sheet1!A2:A101)”

          Next j

Next I

End sub

and ... nothing
but I did something.