sum in visual basic

gkzeta

New Member
Joined
Mar 15, 2019
Messages
10
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,848
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

Code:
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.

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

Well-known Member
Joined
Jul 14, 2008
Messages
2,848
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Assuming you have your data on sheet2 in this way:

Excel Workbook
ABCDE
1PERSONSALARYINCOMEEXPENSESDATE
2John126.04.015/feb/2019
3Han157.55.015/feb/2019
4Fran168.05.315/feb/2019
5Su84.02.715/feb/2019
6John127.04.528/feb/2019
7Han158.55.528/feb/2019
8Fran169.05.828/feb/2019
9Su85.03.228/feb/2019
10John126.04.015/mar/2019
11Han157.55.015/mar/2019
12Fran168.05.315/mar/2019
13Su84.02.715/mar/2019
14John127.04.531/mar/2019
15Han158.55.531/mar/2019
16Fran169.05.831/mar/2019
17Su85.03.231/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
ABC
1
2START DATEEND DATEINCOME
328/feb/201915/mar/201955
sheet1
 

gkzeta

New Member
Joined
Mar 15, 2019
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You have to check the dates, you could give clearer examples, maybe images of what you have on the sheet1 and on the sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,968
Members
409,848
Latest member
Blomsten
Top