(vba) looping frequency function

[montecarlo2012]

Hi every one

A picture isworth a thousand words, agree, I upload a picture .
[image missing]

I am in the chapter of LOOPS, but I don’t see what I really need.

How to loop the “frequency function” in VBA; I didn’t see nothing like that anywhere, I have and array and I would like to have frequency result at least in three different ranges, an get three different tables.

Please check my picture, maybe I make clear the point, otherwise, of course, askme; I know you can help me If I really help. (this is for self-study purpose, please I rather a code than a formula, if you don't mind, thanks). by the way, how to upload my file!!. I don't see the buttom. sorry.


Thanks.

 

[montecarlo2012]

bump

 

[mirabeau]

Really, I have little to no idea of just what you want. And after looking at your pics was unable to achieve revelation.

However, just for interest, your 3rd pic with 0,1,2,3 in F1, G1, H1 and I1, and "this is the bin organize according to the results" in a box - if you want to get that from the material in Cols 1,2 and 3, you could try the following vba code:

Sub zem() 
Dim a(), b()
Dim c As Object, d, e
Dim i&, n&
Set c = CreateObject("scripting.dictionary")
e = Range("A1").CurrentRegion.Resize(, 3)
n = UBound(e, 1)
For i = 2 To n
    d = e(i, 3)
    If Not c.Exists(d) Then
        c(d) = c.Count + 1
        ReDim Preserve b(1 To c(d))
        ReDim Preserve a(1 To n, 1 To c(d))
        b(c(d)) = 2
        a(1, c(d)) = e(i, 3)
        a(2, c(d)) = e(i, 2)
    Else
        b(c(d)) = b(c(d)) + 1
        a(b(c(d)), c(d)) = e(i, 2)
    End If
Next i
With Range("F1").Resize(n, c.Count)
    .Value = a
    .Sort .Rows(1), Orientation:=xlLeftToRight, Header:=xlNo
    .Rows(1).Font.Bold = True
End With

End Sub

 

[montecarlo2012]

after looking at your pics was unable to achieve revelation.

I really appreciate your reply, I understand your code, is to show the results in a bell curve style, I think you wash the pic I posted for different reason, I would like to upload my pic directly here, (I don't know how). any way here is my real question.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	range[4-6]	S	T
1		[1]	[2]	[3]	[4]	[5]	[6]									bin	range[3-5]	range[4-6]	range[5-7]	range[6-8]
2		7	4	1	9	12	3									1	0	3	4	6
3		11	8	6	7	3	4									2	1	3	2	2
4		7	12	12	2	4	6									3	2	1	1	0
5		7	5	7	11	3	4									4	3	2	2	3
6		1	2	2	1	6	1									5	1	1	1	0
7		4	10	7	1	7	6									6	2	2	2	2
8		4	1	4	1	9	11									7	4	3	4	2
9		10	5	12	2	1	12									8	1	0	0	0
10		8	7	11	7	7	1									9	0	0	0	1
11		9	5	5	10	11	7									10	0	0	1	1
12		9	12	5	2	8	2									11	2	1	1	1
13		6	10	8	8	7	1									12	2	2	0	0
14		8	12	2	2	10	4
15		11	1	8	1	2	2
16		3	1	7	3	12	9
17		2	7	8	9	12	8
18		6	10	8	4	12	12
19		9	9	8	7	12	10
20		5	11	11	1	3	9
21		9	5	12	7	1	3
22		12	1	8	7	1	1
23		3	3	12	5	2	1

column"Q" results offrequencyof the rows [3]11-8-6-7-3-4[4]7-12-12-2-4-

[5]7-5-7-11-3-4 , column"R" results of frequencyof the rows[4]7-12-12-2-4-6[5]7-5-7-11-3-4[6]1-2-2-1-6-1 I think now you will see what picture I am talking about, again thanks for your reply, hope to read from you soon.

 

[MickG]

Assuming you want the Frequency of the the Numbers in column "P" based on row 1 columns "Q to T".
Try this:-
NB:- See Comment in code for Range selections Ref columns "Q to T"

Sub MG15Nov23
Dim Rws         As Variant
Dim Ac          As Integer
Dim Rng         As Range
Dim Dn          As Range
Set Rng = Range(Range("P2"), Range("P" & Rows.Count).End(xlUp))


'Change the row below to the ranges you want to consider !!!
Rws = Array("C3:H5", "C4:H6", "C5:H7", "C6:H8")


For Each Dn In Rng
        For Ac = 0 To UBound(Rws)
            Dn.Offset(, Ac + 1) = Application.CountIf(Range(Rws(Ac)), Dn)
        Next Ac
Next Dn
End Sub

Regards Mick

 

[montecarlo2012]

Regards Mick
Thank you MickG, you are the best.

 

[MickG]

You're welcome
Regrds Mick