From macro to vba

[montecarlo2012]

Hello everyone.
I am here trying to see the transformation from a simple Macro to a Code, I know guy for you is something so Simple, but “ sorry “ no for me, I am a new self-thaught bee.
I just sum two numbers with the macro recorder I got this:

Range("H2").Select
ActiveCell.Formula = "=SUM(A2:B2)"

So with my Basic knowledge I see that H2 has to be a variable, sum I can think as a total, or another variable, and the two location as well, so I got this:

Sub real_code()
Dim H As Integer, A As Integer, B As Integer
A = Range("A2").Value: B = Range("B2").Value
H = A + B
Range("H1").Value = H
End Sub

And “”Bingo”” work well.

But now I intent to transform the macro using the function COUNT,

Sub C_m()
Range("H6").Select
ActiveCell.Formula = "=COUNT(B:B)"
End Sub

BUT then, I realize my logic do not worked as before, the only thing
So far, I can see is, must be a loop, how I will deduce that?, “ I don’t know” that why I am here.
Thank you.

 

[DanteAmor]

What do you need?
Forget the macro and the code for a moment, you can explain with your words what you want to do.

 

[montecarlo2012]

wow, thank you so much Mr. D a n t e A m o r, your offer is really Priceless.

explain with your words what you want to do.

Still I am in the same idea of counting my Dynamic Array.

Sub DanteAmor()    Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long
    
    Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
    wMin = WorksheetFunction.Min(r)
    wMax = WorksheetFunction.Max(r)
    r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
    
    wRow = 2
    For i = wMin To wMax
        wCol = 4
        Cells(wRow, "C").Value = i
        For j = wMin To wMax
            Cells(1, wCol).Value = j
            Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
            wCol = wCol + 1
        Next
        wRow = wRow + 1
    Next
    MsgBox "Done"
End Sub

in this code, you know count one column and displays a matrix, so now I would like count a dynamic array of six columns (B2:G2600) and the numbers in this array is a set from one to 53 the maximun number.

	B	C	D	E	F	G
1	15	17	30	42	44	49
2	11	15	18	19	22	49
3	8	15	22	25	40	23
4	12	13	19	20	36	47
5	3	5	7	13	19	48
6	13	15	17	20	44	20
7	3	4	10	23	38	48
8	13	17	27	40	42	42
9	2	6	11	14	31	47
10	3	4	13	28	35	34
11	1	2	3	13	28	46
12	10	23	26	36	37	48
13	12	18	20	32	38	42
14	12	15	17	18	31	42
15	7	14	15	22	37	42
16	6	24	28	40	47	38
17	6	20	26	29	42	48
18	3	5	19	20	27	45
19	2	10	27	31	33	35
20	1	21	24	38	39	45
21	5	17	21	38	41	44
22	16	20	27	31	40	47
23	12	17	30	43	44	41
24	10	16	21	29	41	46
25	3	11	14	15	27	43
26	4	8	24	34	43	40
27	4	11	12	19	31	45
28	9	22	23	29	31	40
29	7	15	24	30	31	44
30	17	19	23	31	33	49

and the matrix will be

	O	P	Q	R	S	T	ETC
	FROM 1	2	3				UNTIL 53
FROM 1
2
3
UNTIL
53

 

[DanteAmor]

And what is the result you expect in the matrix?
You could explain a couple of examples with the data that you put.

 

[montecarlo2012]

And what is the result you expect in the matrix?
You could explain a couple of examples with the data that you put.

So, Sorry DanteAmor

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl64, width: 64, align: right"]1[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]4[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]6[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl63, align: right"]37[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]21[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]26[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

instead of search into one column, this time will count 6 columns and display how many time each number follow each number, the random numbers in this array are a set of 1 to 53. Thank you for your time.

 

[DanteAmor]

You can explain how you get a 37 and how you get a 30

	1	2
1	37	30
2

 

[montecarlo2012]

You can explain how you get a 37 and how you get a 30


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]37[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The fact is, those numbers are just an example, about how looks like.
In other words if I don't have the code I really don't know what the real results will be.
Sorry.

 

[DanteAmor]

The fact is, those numbers are just an example, about how looks like.
In other words if I don't have the code I really don't know what the real results will be.
Sorry.

But to make the code, I need you to explain how to get to the result.

Forget the code for a moment.
What do you want to do? Do you want to add, or to count, what does it mean?

 

[montecarlo2012]

Sorry about all this. and thank you for your patient.
One post before, with Your code I was able to get the results from one column only. Now I would like to get the results no just from one column, I need to COUNT in each column.
COUNT.
More or less let say, is to expand your code, and instead to count only the column in the example, count the whole entire DYNAMIC array.

	B	C	D	E	F	G	H	I	J	K	L	M	N		13
1															12
2	15			1	2	3	4	5	6	7	8	9	10	11	12
3	11		1	37	30	31	29	19	24	24	20	13	10	8	5
4	8		2	30	21	35	21	18	20	16	9	6	2	9	5
5	12		3	25	25	15	22	20	21	13	11	12	14	8	8
6	3		4	29	26	23	21	23	14	20	7	10	13	5	9
7	13		5	23	15	22	25	11	14	10	13	8	5	9	9
8	3		6	24	16	18	16	19	9	16	10	2	8	7	1
9	13		7	21	11	13	11	13	18	7	2	9	10	4	6
10	2		8	12	17	6	11	9	6	5	5	11	5	4	5
11	3		9	16	11	14	13	3	8	7	6	4	3	4	3
12	1		10	19	8	10	10	4	5	5	5	7	2	2	6
13	10		11	10	9	8	8	6	7	5	5	5	2	3	2
14	12		12	9	11	6	9	5	5	6	9	1	4	4	3
15	12		13	9	10	9	11	10	3	3	6	5	3	5	1
16	7		14	9	6	3	8	7	2	3	0	6	1	4	2
17	6		15	12	9	4	3	8	0	8	2	5	7	1	2
18	6		16	5	5	4	7	1	5	2	1	0	3	5	6
19	3		17	4	3	4	1	2	2	4	0	3	4	1	0
20	2		18	7	2	2	3	1	2	2	4	3	2	1	1
21	1		19	0	0	4	0	3	4	4	3	0	1	1	2
22	5		20	3	2	1	3	1	2	2	4	1	1	1	1
23	16		21	4	2	3	1	2	1	1	2	2	0	0	0
24	12		22	0	2	1	3	1	1	1	1	0	0	1	0
25	10		23	1	2	0	0	4	4	0	0	0	0	1	1
26	3		24	3	1	1	1	0	3	1	0	1	1	0	1
27	4		25	0	2	0	0	1	1	1	0	0	0	0	1
28	4		26	1	2	2	0	4	0	0	0	0	0	0	0
29	9		27	0	0	0	0	0	0	0	0	0	1	0	0
30	7		28	0	0	1	0	2	1	1	0	1	0	0	0
31	17		29	1	0	1	0	0	0	0	1	0	0	0	0
32	12		30	1	0	1	0	0	0	0	0	2	0	0	0
33	8		31	0	0	1	1	0	0	0	0	0	0	0	0
34	15		32	0	0	0	1	0	0	0	0	0	0	0	0
35	1		33	0	0	0	0	0	0	0	0	0	0	0	0
36	20		34	0	0	0	0	0	0	0	0	1	0	0	0
37	2		35	0	0	1	1	0	0	0	0	0	0	0	0
38	5		36	0	0	0	0	0	0	0	0	0	0	0	0

AND THIS IS YOUR CODE FROM THE LAST POST.

  Sub g_n()    Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long
    
    Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
    wMin = WorksheetFunction.Min(r)
    wMax = WorksheetFunction.Max(r)
    r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
    
    wRow = 2
    For i = wMin To wMax
        wCol = 4
        Cells(wRow, "C").Value = i
        For j = wMin To wMax
            Cells(1, wCol).Value = j
            Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
            wCol = wCol + 1
        Next
        wRow = wRow + 1
    Next
    MsgBox "Done"
End Sub

 

[DanteAmor]

Try this.

The result will begin in column I

Sub g_n()
    Dim r As Range, wMin As Double, wMax As Double, wRow As Long
    Dim cini As Long, wCol As Long, i As Long, j As Long
    
    Application.ScreenUpdating = False
    Set r = Range([COLOR=#0000ff]"B1", Range("G" & Rows.Count).End(xlUp)[/COLOR])
    wMin = WorksheetFunction.Min(r)
    wMax = WorksheetFunction.Max(r)
    cini = r.Columns.Count + 3
    Range(Cells(1, cini), Cells(Rows.Count, Columns.Count)).ClearContents
    
    wRow = 2
    For i = wMin To wMax
        wCol = cini + 1
        Cells(wRow, cini).Value = i
        For j = wMin To wMax
            Cells(1, wCol).Value = j
            Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
            wCol = wCol + 1
        Next
        wRow = wRow + 1
    Next
    Application.ScreenUpdating = False
    MsgBox "Done"
End Sub

Note:
For a matrix of: B2:G2600, the macro will take a long time.