Subtotal by Month not Day?

[ungers]

Hi,

I have created some subtotals in some columns of data which for example are the dates, names, and amounts of invoices.

The code I used is:

Range(Range("C1"), Range("C1").End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3)

This is working of sorts but it is giving me subtotals based on the day in the date column. How can I change this to group by the month?

Thanks for your help!

 

[Brianwarnock]

You will need to use the year and month functions to concatenate these into a fresh column to do the Groupby on. You may still have problems with the sort order and additional work with further hidden columns may be required.

Brian

 

[ungers]

Thanks Brian for the pointer...

I understand what you are saying and have tried a few things but i'm a but stuck from the beginning...what's the best way to copy to my date column to a new one using the month function?!!

 

[ungers]

So I've managed to use:

lastrow = Range("C1").End(xlDown).Row
Range("D2" & lastrow) = Month(Range("A2"))

To copy the first date in A2 and paste it to every cell in range D.

But when I modified it to:

Range("D2" & lastrow) = Month(Range("A2:A" & lastrow))

I get an error.

Am I barking up th wrong tree? How can I take the month from the range of dates and paste it into new column?

 

[Brianwarnock]

Do you have to do all of this in VBA, my idea was to use a Worksheet formula when creating the year month column.

Assuming date column is A in another column

=CONCATENATE(YEAR(A1),IF(LEN(MONTH(A1))=1,CONCATENATE(0,MONTH(A1)),MONTH(A1)))

and copy down

For jan sept and oct of this year you would get

201301
201309
201310

Thus sorting would be ok.

To do this with code I think would require a loop iterating the row number, unless you can copy the formula down and the references change, never done it and as I mentioned in the other thread I donot have access to a machine with Excel at the moment.

Brian

 

[ungers]

So here is where I have got to with this,

I have managed to copy my range of dates to a new column with the following:

lastrow1 = Range("D1").End(xlDown).Row
Range("A2").Formula = "=Month(B2)"
Range("A2").Copy
Range("A3:A" & lastrow1).PasteSpecial

And then successfully create subtotals based on the month with:

Range(Range("C1"), Range("C1").End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4)

I have even then got as far in the tidying up as removing the first set of months to leave just the subtotal using:

lastrow2 = Range("A1").End(xlDown).Row
With Range("A1:A" & lastrow2)
Set RngFnd = .Find(Total).
Set rngDelete = .ColumnDifferences(Comparison:=RngFnd)
rngDelete.ClearContents


So where I am now stuck is:

In first instance when I copy the dates, how can I make this display as full month name rather than a number?

Secondly my tidying up only goes as far the first subtotal, what am I missing to make the find/clear contents code keep working down the range?

 

[Brianwarnock]

As there is no Format function in Excel for dates , which would have made the whole exercise easier, I think that you will need to create a table in another sheet,
1 January
2 February
Etc and use Vlookup.

The cell containing the Date or month ie col A is presumably empty in the totals row , hence the end xldown will stop at that row.
Is there a column that always contains data? If so use that column.

If not you may have to use the xlup approach to find the last row.

Brian

 

[Brianwarnock]

Just realised that you could use currentregion to find the last row, something like

Range("A1").Currentregion.rows.count

Look it up in help.

Brian

 

[Brianwarnock]

As there is no Format function in Excel for dates , which would have made the whole exercise easier, I think that you will need to create a table in another sheet,
1 January
2 February
Etc and use Vlookup.


Brian

Other options are to use Switch or Select Case, only you can decide which is easier to include in your code and I think that you are capable of that.

Brian

 

[ungers]

Right then I solved this one, Thanks Brian for the tip on the select case.

Here's what I did:

Firstly extract the month, then run the subtotals, use select case and a loop to pick out the totals text and put something more useful back into the date column, delete the first column.

Column A is firstly the date, then a blank.
Column B is the date after insert
Column D/4 is the amounts

Dim lastrow As Long
Dim rtext As String

lastrow = Range("A1").End(xlDown).Row
'Find Last row 

Range("A:A").EntireColumn.Insert 
'Create a column to paste the dates into

Range("A2").Formula = "=Month(B2)"
Range("A2").Copy
Range("A3:A" & lastrow).PasteSpecial
Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4)
'Extracts the month from the date column, pastes the formula down the range, and then runs the subtotals.

Set r = Range("A2")
'Sets first cell

Do

Set r2 = r.Offset(0, 1)
rtext = r.value
'set r2 to be the next cell across which is where the new text is going to go.
'rtext is the string to compare

Select Case rtext

Case "1 Total"
r2.value = "January Total :"

'etc etc until

Case "Grand Total"
r2.value = "Grand Total :"

End Select

Set r = r.Offset(1, 0)
'sets r to be next cell down the range

Loop Until rtext = "Grand Total"
'where we want to end

Range("A:A").ClearContents

 

[Brianwarnock]

Thanks for the update, I thought that you would be capable with a just a few pointers.

Brian