From macro to vba

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
Code:
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.
Code:
[TABLE="width: 259"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]49[/TD]
[/TR]
</tbody>[/TABLE]
and the matrix will be
Code:
[TABLE="width: 512"]
 <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"] [/TD]
  [TD="class: xl66, width: 64"]O[/TD]
  [TD="class: xl66, width: 64"]P[/TD]
  [TD="class: xl66, width: 64"]Q[/TD]
  [TD="class: xl66, width: 64"]R[/TD]
  [TD="class: xl66, width: 64"]S[/TD]
  [TD="class: xl66, width: 64"]T[/TD]
  [TD="class: xl66, width: 64"]ETC[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"]FROM   1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"]UNTIL   53[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]FROM    1[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]UNTIL[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]53[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
And what is the result you expect in the matrix?
You could explain a couple of examples with the data that you put.
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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
1234567
137302529232421
230212526151611
331351523221813
429212221251611
519182023111913
62420211414918
72416132010167
820911713102
91361210829
1010214135810

<tbody>
</tbody>
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
You can explain how you get a 37 and how you get a 30


12
13730
2

<tbody>
</tbody>
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
You can explain how you get a 37 and how you get a 30


12
13730
2

<tbody>
</tbody>
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
Code:
[TABLE="width: 1024"]
 <colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"] [/TD]
  [TD="class: xl65, width: 64"]B[/TD]
  [TD="class: xl65, width: 64"]C[/TD]
  [TD="class: xl65, width: 64"]D[/TD]
  [TD="class: xl65, width: 64"]E[/TD]
  [TD="class: xl65, width: 64"]F[/TD]
  [TD="class: xl65, width: 64"]G[/TD]
  [TD="class: xl65, width: 64"]H [/TD]
  [TD="class: xl65, width: 64"]I [/TD]
  [TD="class: xl65, width: 64"]J[/TD]
  [TD="class: xl65, width: 64"]K[/TD]
  [TD="class: xl65, width: 64"]L[/TD]
  [TD="class: xl65, width: 64"]M[/TD]
  [TD="class: xl65, width: 64"]N[/TD]
  [TD="class: xl65, width: 64"] [/TD]
  [TD="class: xl65, width: 64, align: right"]13[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]12[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]12[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]5[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]5[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]8[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]9[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]9[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]6[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]5[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]6[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]6[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
  [TD="class: xl65, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
AND THIS IS YOUR CODE FROM THE LAST POST.
Code:
  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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
Try this.

The result will begin in column I

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,902
Messages
5,483,613
Members
407,400
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top