Excel VBA to Read Range of Dates

[whcmelvin]

hi,

Could someone teach me on how to allow excel to to read a range of dates that is selected in 2 listbox using VBA?

Eg.

Listbox1 is 25/05/2015 and listbox2 is 30/05/2015.

Then the VBA code would allow excel to read the the dates from 25 to 30 May and sum the values.
Using the table below, excel will sum column 2 and return 21.

22/05/2015	10
23/05/2015	12
24/05/2015	14
25/05/2015	1
26/05/2015	2
27/05/2015	3
28/05/2015	4
29/05/2015	5
30/05/2015	6

<tbody>
</tbody>

Please Help guys

Thanks in advance!

Regards
Melvin

 

[whcmelvin]

Could someone help please.

Thanks lots

 

[Snakehips]

Melvin,

Not sure of your setup but assuming Activex list boxes and dates A2:A?? then try something like..

Sub Date_Total()
lr = Cells(Rows.Count, "A").End(xlUp).Row
Strt = ListBox1.Value
Fin = ListBox2.Value
Set DtRng = Range("A2:A" & lr)
Set SumRng = DtRng.Offset(0, 1)


Range("C2") = WorksheetFunction.SumIfs(SumRng, DtRng, ">=" & Strt, DtRng, "<=" & Fin)


End Sub


Private Sub ListBox1_Change()
Call Date_Total
End Sub

Hope that helps.






Private Sub ListBox2_Change()
Call Date_Total
End Sub

[/code]

 

[whcmelvin]

Thank for your help.

I tried out the code you provided.
Do you mind if you could help me in optimizing the code? The sumifs code runs quite slowly.
Here is the code.

NextRow = Application.WorksheetFunction.CountA(Range("F:F")) + 1Set r = Range(Cells(2, 6), Cells(NextRow, 6))
For n = 2 To r.Rows.Count
    Cells(n, 7).Value = WorksheetFunction.SumIfs(SumRng, ReportType, "7F", DateSel, Cells(n, 6))
    Sheets("Sheet1").Select
Next n

Thanks and regards
Melvin

 

[Snakehips]

I'm somewhat confused.
Can you explain how your above code relates to the data you posted and code I provided?
Why the looping?
ReportType?DateSel?
Column F?

 

[whcmelvin]

Below is the set up of my excel sheet and the code I am using. First, there are two listboxes(IOSDate1 and IOSDate2) and user will select the dates. E.g. IOSDate1 = 24/05/2015 and IOSDate2 = 30/05/2015

The code will then get the values between 2 dates and copy paste into F2(DateSel) Column.

I did this because Sumifs's criteria range need to be the same as the sum range.

The loop is because for every dates under the column DateSel, a sumifs will be required.

After the Loop, The code will then remove duplicates date as shown below.

DateRng	SumRng	ReportType			DateSel	1F	7F
24/5/2015	100	1F			24/5/2015
24/5/2015	1	7F			25/05/2015
25/05/2015	200	1F			26/05/2015
25/05/2015	2	7F			27/05/2015
26/05/2015	300	1F			28/05/2015
26/05/2015	3	7F			29/05/2015
27/05/2015	400	1F			30/05/2015
27/05/2015	4	7F
28/05/201	500	1F
28/05/201	5	7F
29/05/2015	600	1F
29/05/2015	6	7F
30/05/2015	700	1F
30/05/2015	7	7F
31/05/2015	800	1F
31/05/2015	8	7F

<tbody>
</tbody>

Dim NextRow As Long    Dim r As Range
    Dim n As Long



    Sheets("Sheet1").Select


    Set FindIOSDate1 = Sheets("Sheet1").Columns("A").Find(IOSDate1, LookIn:=xlValues, lookat:=xlWhole)
   Set FindIOSDate2 = Sheets("Sheet1").Columns("A").Find(IOSDate2, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious)


Worksheets("Sheet1").Range(Cells(FindIOSDate1.Row, 1), Cells(FindIOSDate2.Row, 1)).Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("F2")


Set DtRng = Range(Cells(FindIOSDate1.Row, 1), Cells(FindIOSDate2.Row, 1))
Set SumRng = DtRng.Offset(0, 1)
Set ReportType = DtRng.Offset(0, 2)
Set DateSel = DtRng.Offset(0, 5)


Cells(1, 6).Value = "Date"
NextRow = Application.WorksheetFunction.CountA(Range("F:F")) + 1
Set r = Range(Cells(2, 6), Cells(NextRow, 6))
For n = 2 To r.Rows.Count


        Cells(n, 8).Value = WorksheetFunction.SumIfs(SumRng, ReportType, "7F", DateSel, Cells(n, 6))
        Cells(n, 7).Value = WorksheetFunction.SumIfs(SumRng, ReportType, "1F", DateSel, Cells(n, 6))
    
    Sheets("Sheet1").Select
Next n
Worksheets("Sheet1").Range("F:H").RemoveDuplicates Columns:=Array(1), Header:=xlYes
Unload DateRange




End Sub

Thanks and regards
Melvin

 

[Snakehips]

Melvin, I am not a user of Pivot Tables but is this not what you need to easily extract this data?

 

[whcmelvin]

You are right. I hardly use Pivot table. Thanks for the reminder.