Hi All,
I have data in a sheet where I need to summarize this data by name and category within two dates, I have done it by name, but I also need it by name and category within specified dates. I have attached a workbook with my code.
The code for the summary by name works perfectly, I need help to summarize by name going down the rows and category by columns within the specified dates. Please keep the code simple for me to understand.
My file is attached.
Thanks in advance.
Mustafa
Sub UsingDates2()Please Login or Register to view this content.
Dim sht As Worksheet
Dim lastrow As Long
Dim names() As String
Dim namecount As Long
Dim rptsht As Worksheet
Dim x As Long
Dim i As Long
Dim currentname As String
Dim totals() As Long
Dim startdate As Integer
Dim enddate As Integer
Dim rng As Range
Set sht = Sheets("Sales")
'rpthsht is the output sheet
Set rptsht = Sheets("By Date")
startdate = sht.Cells.Find(what:=rptsht.Range("A2"), LookIn:=xlValues).Column ' there is an input cell for the start date
enddate = sht.Cells.Find(what:=rptsht.Range("B2"), LookIn:=xlValues).Column ' there is an input cell for the end date
lastrow = sht.Cells(Rows.Count, 1).End(xlUp).Row
rptsht.Range("D:E").Columns.Clear
sht.Range("A1:A" & lastrow).AdvancedFilter _
xlFilterCopy, copytorange:=rptsht.Range("D2"), Unique:=True
With rptsht.Range("D2")
namecount = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
ReDim names(namecount)
ReDim totals(namecount)
For x = 1 To namecount
names(x) = .Offset(x, 0).value
Next x
End With
With sht.Range("A1")
For x = 1 To Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
currentname = .Offset(x, 0).value
For i = 1 To namecount
If currentname = names(i) Then
Set rng = sht.Cells(x + 1, startdate).Resize(1, (enddate - startdate + 1))
totals(i) = totals(i) + .Offset(x, 2).value * _
Application.WorksheetFunction.Sum(sht.Range(rng.Address))
End If
Next i
Next x
End With
With rptsht.Range("D2")
.Offset(0, 1).value = "Totals"
For i = 1 To namecount
.Offset(i, 1).value = totals(i)
Next i
Range(.Offset(0, 0), .Offset(0, 1).End(xlDown)).Select
End With
End SubPlease Login or Register to view this content.
Bookmarks