sum in visual basic

[gkzeta]

Hi
I have a workbook with two sheets. To the second sheet (sheet2) I have 20 people in, each person has 3 columns salary, income, expenses.
On Sheet 1, I want to be able to enter a start date (A2) and an end date (A3). Then I want a VBA to search each person and sum the incomes between these two dates and show me this sum on sheet1 on a specified cell (A3).
I try another way with the function countifs but I have to change the number of line each every time.
I have already made a code that copies the last cell, so I can’t select the entire column.

 

[lrobbo314]

A few things. First off, you never say if you have dates on Sheet2. I am assuming you do. So, this code assumes that on Sheet 2, Names in Column A, Date in Column B, Salary Column C, Income Column D, Expenses Column E. Also, You say that on Sheet 1 you are putting your end date in A3, but then you say that you want the result to show up in A3 as well. I am assuming this is a typo and the code below puts the result in A4.

You may need to make adjustments to the code if these assumptions are incorrect.

Having said that, add the following code to the sheet code for sheet 1. Right click on the tab that says "Sheet1" and click 'View Code' and paste this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$3" Then SumIncomeBetweenDates
Application.EnableEvents = True
End Sub

Then in a standard module insert the following code.

Sub SumIncomeBetweenDates()
Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
Dim AR() As Variant: AR = ws2.Range("A2:E" & ws2.Range("A" & Rows.Count).End(xlUp).Row()).Value
Dim d1 As Date: d1 = ws1.Range("A2").Value
Dim d2 As Date: d2 = ws1.Range("A3").Value
Dim IncomeTotal As Double: IncomeTotal = 0#


For i = LBound(AR) To UBound(AR)
    If AR(i, 2) >= d1 And AR(i, 2) <= d2 Then
        IncomeTotal = IncomeTotal + AR(i, 4)
    End If
Next i


ws1.Range("A4").Value = IncomeTotal
End Sub

What this does is as soon as you enter an end date, the code will fire and give you your result.

Let me know if you have any questions.

 

[lrobbo314]

As a side note, if it were me, I wouldn't use this kind of VBA solution for this problem. I would simply turn your data in Sheet2 into a PivotTable and then use slicers to adjust the date range.

 

[DanteAmor]

Assuming you have your data on sheet2 in this way:

Excel Workbook
	A	B	C	D	E
1	PERSON	SALARY	INCOME	EXPENSES	DATE
2	John	12	6.0	4.0	15/feb/2019
3	Han	15	7.5	5.0	15/feb/2019
4	Fran	16	8.0	5.3	15/feb/2019
5	Su	8	4.0	2.7	15/feb/2019
6	John	12	7.0	4.5	28/feb/2019
7	Han	15	8.5	5.5	28/feb/2019
8	Fran	16	9.0	5.8	28/feb/2019
9	Su	8	5.0	3.2	28/feb/2019
10	John	12	6.0	4.0	15/mar/2019
11	Han	15	7.5	5.0	15/mar/2019
12	Fran	16	8.0	5.3	15/mar/2019
13	Su	8	4.0	2.7	15/mar/2019
14	John	12	7.0	4.5	31/mar/2019
15	Han	15	8.5	5.5	31/mar/2019
16	Fran	16	9.0	5.8	31/mar/2019
17	Su	8	5.0	3.2	31/mar/2019
sheet2

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="" target="_blank"> </a>

On sheet1 you can put the following formula

Excel Workbook
	A	B	C
1
2	START DATE	END DATE	INCOME
3	28/feb/2019	15/mar/2019	55
sheet1

 

[gkzeta]

hi
the result which shows me in cell A4 has the number 0.
I put on Sheet 2, Names in Column A, Date in Column B, Salary Column C, Income Column D, Expenses Column E.


On my Sheet 2, i have Date in Column A, name in Column B, Salary Column C, Income Column D which I deleted it is not useful, Expenses Column E, etc.
I want in cell A4 of sheet1 to display all the expenses of specific dates, that is, the dates I choose.

 

[DanteAmor]

You have to check the dates, you could give clearer examples, maybe images of what you have on the sheet1 and on the sheet2

 

[gkzeta]

Thanks for your response.
I finally use sumifs function.

 

[DanteAmor]

You're welcome.