FedericoForb
03-11-2020, 02:36 AM
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.
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.