How to transfer data from an array to a matrix?

[FedericoForb]

Good morning,
I hope my issue could be helpful to other users in the community (and to get help with my problem, of course). It is about macro in VBA.

Very briefly, I am trying to fill a matrix on a worksheet in excel with some values located in an array I defined and filled previously. The following is my code: as you can see I am working through functions but some subs would be ok as well


Public Function CriticalityMatrix_Sheet(Sheet As Worksheet)
'Clear values on target sheet
'Sheet.Range(Sheet.Cells(InitRow, InitColumn), Sheet.Cells(LastRow, LastColumn)).ClearContents

'Define 'mat_RBS', which is the matrix read from Sheet 'RBS'
Dim mat_RBS() As Variant
mat_RBS = WS_R_RBS.Range(WS_R_RBS.Cells(5, 1), WS_R_RBS.Cells(500, 63)).Value
'Hint: start from 1 Column


'
' 'Define 'arr_COUNT_CM_G_P' (Gross, Positioning)
Dim arr_COUNT_CM_G_P() As Integer 'I created a dynamic array
ReDim arr_COUNT_CM_G_P(1 To 25) 'Hint: in general, when you change dimension of array/matrix, "ReDim PRESERVE array" will prevent the contents of the array from being deleted
For i = 1 To 25
arr_COUNT_CM_G_P(i) = 0 'I set the value of each i = 0
Next i
'Fill 'arr_COUNT_CM_G_P', COMPARING values with related column in 'mat_RBS'
Dim COUNT As Integer 'I created a new variable, integer
COUNT = 0
For i = 1 To 25 'initialize the for loop to populate arr_COUNT...
COUNT = 0 'at each loop, COUNT should restart from zero (and not from the COUNT of the preceding i-1 step o the loop. In that case, COUNT could be different from zero)
For j = 1 To UBound(mat_RBS)
If mat_RBS(j, 37) = i Then 'It would refers to "Matrix Positioning GROSS" column, that is, column 37
If Sheet.Name = WS_R_CM_T.Name Then
If mat_RBS(j, 34) >= 0 Then 'It refers to Average Gross Risk Value (if >=0 it is a risk, if <0 it is an opportunity 'TargetColumn to be defined depending on position of column in 'mat_RBS'
COUNT = COUNT + 1
End If
ElseIf Sheet.Name = WS_R_CM_O.Name Then 'da correggere, else if?
If mat_RBS(j, 34) < 0 Then 'TargetColumn to be defined depending on position of column in 'mat_RBS'
COUNT = COUNT + 1 'con +1 conta una rilevazione in più, con +0 è esatto, non funziona con elseif sheetname...
End If
End If
Next j 'ripeti il loop per ogni riga della matrice RBS
arr_COUNT_CM_G_P(i) = COUNT 'set the value of position i in arr_COUNT...
Next i

My next move will be to define a new matrix to fill with the value of the array (I would like, for example, to fill the cell 1,1 with the value contained in the array(1)) and, as last step, copying such matrix on the sheet I am working on.

First post, hoping I respected all of the guidelines.

Thank you for any suggestions.

[snb]

Sub M_snb()
redim sn(10,20)

for j=0 to ubound(sn)
for jj=0 to ubound(sn,2)
sn(j,jj)=j*jj
next
next

sheet1.cells(1).resize(ubound(sn)+1,ubound(sn,2)+1)=sn
End Sub

[FedericoForb]

Sub M_snb()
redim sn(10,20)

for j=0 to ubound(sn)
for jj=0 to ubound(sn,2)
sn(j,jj)=j*jj
next
next

sheet1.cells(1).resize(ubound(sn)+1,ubound(sn,2)+1)=sn
End Sub

I am really grateful for your suggestion.

For whoever is going to read this thread, in the following there is the way I cope with my problem:

Dim mat_GP() As Integer ReDim mat_GP(1 To 5, 1 To 5)

'Populate mat_GP from values in arr_COUNT_CM_G_P

mat_GP(1, 1) = arr_COUNT_CM_G_P(1)
'...
mat_GP(1, 2) = arr_COUNT_CM_G_P(19)

'Write mat_GP on sheet T/O

WS_R_CM_T.Range(WS_R_CM_T.Cells(6, 4), WS_R_CM_T.Cells(10, 8)).Value = mat_GP

As you can see, without any loops, I simply rewrite the array inside my matrix, cell by cell.

Thank you for the support.