Conditional sum with DATE in VBA

[FedericoForb]

Good afternoon,
The following is not an easy question but, I hope, something can help me with that.

Basically what I want to do is a conditional sum (SUMIFS) in Vba.

I would like to sum certain values contained in a worksheet of mine (VBS is its name). IF and only IF the corresponding initial and final DATE (such values are associated to an event with an INITIAL date and an END date) are between a certain date range (monthly).

Obviously I also need to loop over the rows of VBS to "catch" all the values.

Let us say that an event which value is 500$ starts 02/04/2020 and ends 04/07/2020: in this case I would like to pick the value (500$) associated to the event and use it to populate the second, third and 4th position of my 1D array "arr_date" -> arr_date (1) = arr_date (2)=arr_date(3)= 500.

Does anyone have an idea about how to cope with the problem?

Thank you to all.

:banghead:

[p45cal]

Please attach a workbook to experiment on.

[Paul_Hossler]

Let us say that an event which value is 500$ starts 02/04/2020 and ends 04/07/2020: in this case I would like to pick the value (500$) associated to the event and use it to populate the second, third and 4th position of my 1D array "arr_date" -> arr_date (1) = arr_date (2)=arr_date(3)= 500.

Also, since I find the rules a little fuzzy, please include some examples of the expected results

I'm not understanding 1) the array, and 2) the need for SUMIFS

I'm guessing your array is 0-based, but you're just using 1-3??

[FedericoForb]

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)???