[VBA] How to loop the frequency function

[montecarlo2012]

Hi.
I have been doing manually the frequency function, I would like to find how to loop this, by hand will take forever what I am doing, here it is what I got until now.

Sub FREQ_B2()
'B2 TO F10 RESULTS ON CN-CO         HIGHLIGHT B1 IS EMPTY

Range("I2:I37").FormulaArray = "=FREQUENCY(B2:F10,H2:H37)"
Range("H2:I37").Copy
Range("CN2").PasteSpecial Paste:=xlPasteValues
End Sub

Sub FREQ_B3()
'B3 TO F11                      RESULTS ON ck-cl highlight b2

Range("I2:I37").FormulaArray = "=FREQUENCY(B3:F11,H2:H37)"
Range("H2:I37").Copy
Range("CK2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_B4()
'B4 TO F12                       RESULTS ON CH/CL HIGHLIGHT B3

Range("I2:I37").FormulaArray = "=FREQUENCY(B4:F12,H2:H37)"
Range("H2:I37").Copy
Range("CH2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_B5()
'B5 TO F13                       RESULTS ON CE/CF HIGHLIGHT B4

Range("I2:I37").FormulaArray = "=FREQUENCY(B5:F13,H2:H37)"
Range("H2:I37").Copy
Range("CE2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_6()
'B6 TO F14                       RESULTS ON CB/CC HIGHLIGHT B5

Range("I2:I37").FormulaArray = "=FREQUENCY(B6:F14,H2:H37)"
Range("H2:I37").Copy
Range("CB2").PasteSpecial Paste:=xlPasteValues

End Sub
Sub FREQ_B7()
'B7 TO F15                       RESULTS ON BY/BZ HIGHLIGHT B6

Range("I2:I37").FormulaArray = "=FREQUENCY(B7:F15,H2:H37)"
Range("H2:I37").Copy
Range("BY2").PasteSpecial Paste:=xlPasteValues

End Sub

Sub FREQ_B8()
'B8 TO F16 RESULTS ON CN-CO         HIGHLIGHT B7

Range("I2:I37").FormulaArray = "=FREQUENCY(B8:F16,H2:H37)"
Range("H2:I37").Copy
Range("BV2").PasteSpecial Paste:=xlPasteValues
End Sub

Sub FREQ_B9()
'B9 TO F17                      RESULTS ON ck-cl highlight b8

Range("I2:I37").FormulaArray = "=FREQUENCY(B9:F17,H2:H37)"
Range("H2:I37").Copy
Range("BS2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_B10()
'B10 TO F18                       RESULTS ON CH/CL HIGHLIGHT B9

Range("I2:I37").FormulaArray = "=FREQUENCY(B10:F18,H2:H37)"
Range("H2:I37").Copy
Range("BP2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_B11()
'B11 TO F19                       RESULTS ON CE/CF HIGHLIGHT B10

Range("I2:I37").FormulaArray = "=FREQUENCY(B11:F19,H2:H37)"
Range("H2:I37").Copy
Range("BM2").PasteSpecial Paste:=xlPasteValues
End Sub
Sub FREQ_B12()
'B12 TO F20                       RESULTS ON CB/CC HIGHLIGHT B11

Range("I2:I37").FormulaArray = "=FREQUENCY(B12:F20,H2:H37)"
Range("H2:I37").Copy
Range("BJ2").PasteSpecial Paste:=xlPasteValues

End Sub
Sub FREQ_B13()
'B13 TO F21                       RESULTS ON BY/BZ HIGHLIGHT B12

Range("I2:I37").FormulaArray = "=FREQUENCY(B13:F21,H2:H37)"
Range("H2:I37").Copy
Range("BG2").PasteSpecial Paste:=xlPasteValues

End Sub

A macro is worth a thousand words
Thanks.

 

[mse330]

Hi montercarlo,

Is this what you're looking for ?

Sub test()

Dim c&, Rg As Range
c = 92
Set Rg = Range("B2:F10")

For x = 1 To 12
   Range("I2:I37").FormulaArray = "=FREQUENCY(" & Rg.Address(0, 0) & ",H2:H37)"
   Range("H2:I37").Copy
   Cells(2, c).PasteSpecial Paste:=xlPasteValues

   Set Rg = Rg.Offset(1)
   c = c - 3
Next

End Sub

 

[montecarlo2012]

mse330 Hello, Thanks for your intervention. I run your code and all I got was #N/A on I4:I37, after this, jump to column "BH" until column "CO" and again the code display the same #N/A.

 

[mse330]

Do you have any data in range H2:H37 & range B2:F10 ? The code is replicating what your code was doing. Try to step through the code & see what's happening ... Just press F8 and you'll what's happening line by line

 

[montecarlo2012]

mse330 thanks for your interest in my case.
So, I tried to follow your though process
my frequency function is really something like this:

C& or C as long
the numbers are moving one at the time (+1), H2:H37 and I2I37 are like constants. Paste, CN is the 92 place to start and CK the next meaning three places left (-3) so C = C - 3
there are two lines I would like to hear from you, how you process that, or better say what logic steps you go follow to arrived to address(0,0) and offset(1), I am really working on this and thank you again for your time.
thank you

 

[mse330]

Yes, you're correct about the first part of the code

Regarding Range.Address returns absolute address of the range & adding (0,0) means (False, False) ... To test it, you can add Debug.Print Rg.Address, Rg.Address(0,0) & see the difference in the immediate window

By its name, Offset is used to offset your range, I used offset(1) to change my range by 1 row - It could be used for rows or columns Offset( row, column) ... Again to test it, Debug.Print Rg.Address, Rg.offset(1).Address, Rg.offset(0,1).Address,Rg.offset(1,1).Address

Hope that makes sense

 

[montecarlo2012]

Thank you so much for taking time for me. a lot sense, you explained really good, thank you for that. I have been looking for that, because I have another code that include those key words and then my progress stop right on address and offset, so I really appreciate your lesson, I will test your idea.

 

[mse330]

Glad I could help