Public Sub TEST(Optional silent As Boolean = False)
[... ]
j = 0
For i = 1 To UBound(mat_Rischi)
ErrorHandler_RepeatCycle1:
j = j + 1
If mat_RBS(j, 17) = "Active" Then
mat_Rischi(i, 4) = mat_RBS(j, 12) ' Event
mat_Rischi(i, 5) = mat_RBS(j, 22) ' Exposure Start Date
mat_Rischi(i, 6) = mat_RBS(j, 23) ' Exposure Finish Date
mat_Rischi(i, 7) = mat_RBS(j, 34) ' Risk Expected Value (Gross)- EUR -
Else
GoTo ErrorHandler_RepeatCycle1
End If
Next i
WS_R_GA.Range(WS_R_GA.Cells(InitRow, 1), WS_R_GA.Cells(LastRow, LastColumn)).Value = mat_Rischi
'I need to define 3 array: "Sum_Total (60)" where to store the sum of Gross Risk Exp. for each month
'and arr_dateSD(60,1) together with arr_dateED, containing START DATE and END DATE of each month in period 2020-2024, with which to compare SD and ED of each EVENT
'Define the matrix "Sum_Total" to paste on the sheet
Dim arr_dateSD As Variant
ReDim arr_dateSD(0 To 59)
For i = 0 To UBound(arr_dateSD)
arr_dateSD(i) = DateAdd("m", i, WS_R_C.Range("G8"))
Next i
'How to avoid to start the lop from j=0? Use "DateAdd" not from G8 but from one month before!
' arr_dateSD_Support = DateAdd("m", -1, WS_R_C.Range("G8"))
' arr_dateSD(i) = DateAdd("m", i, arr_dateSD_Support)
Dim arr_dateED As Variant
ReDim arr_dateED(1 To 60)
For i = 1 To UBound(arr_dateED)
arr_dateED(i) = DateAdd("m", i, WS_R_C.Range("G8"))
Next i
Dim Sum_Total() As Variant
ReDim Sum_Total(1 To 60)
'loooop
' For Z = 1 To UBound(arr_dateED)
j = 0
For i = 1 To UBound(mat_Rischi)
For j = 1 To UBound(Sum_Total)
'If arr_dateSD(j - 1) < mat_Rischi(i, 5) < arr_dateED(j) Then
' If mat_Rischi(i, 6) > arr_dateED(j) And mat_Rischi(i, 5) > arr_dateSD(j - 1) Then
If mat_Rischi(i, 5) < arr_dateED(j) < mat_Rischi(i, 6) Then
Sum_Total(j) = Sum_Total(j) + mat_Rischi(i, 7)
End If
Next j
Next i
Above you can see part of my code.
My question is: say that a project with a risk expected value of 500 starts 20.02 and ends 20.05, how can I assign such "500" to the MONTH 2,3,4,5 (with start date 15.02, 15.03, 15.04, 15.05 and end date 15.03, 15.04, 15.05, 15.06 respectively)???