VBA to Sum each variable range in a column separately

[irfananeeza]

Hi Experts,

After spending couple hours on google I decided to ask for help. I have created a vba code which (A) converts date text to column (B) Sort the data by month (C) Checks If current month different from previous month (D) When months are different, Insert an entire row few times (E) Also, add titles to each separated month.

When data is separated into months, I want to SUM TOTAL each month but I am unable to figure it out. In brief, I'd like to see monthly sum in Column B next to "Total" in Column A. I would appreciate your help. Here is my VBA Code. Thanks.

Sub Macro4()
Dim lastrw As Integer
Dim ChkRw As Integer

lastrw = Range("A" & Rows.Count).End(xlUp).Row
        Columns("A:A").TextToColumns Destination:=Range("A1")
        Range("A1:C" & lastrw).SORT Key1:=Range("A1")
     
    For ChkRw = lastrw To 1 Step -1
    If Left(Range("A" & ChkRw), 2) <> Left(Range("A" & ChkRw + 1), 2) Then

        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("A" & ChkRw + 1).Value = "Date"
        Range("B" & ChkRw + 1).Value = "Amount"
        Range("C" & ChkRw + 1).Value = "Description"
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("B" & ChkRw + 1).Value = Range("D1") 
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
        Range("A" & ChkRw + 1).Value = "Total"
        Range("A" & ChkRw + 1).EntireRow.Insert Shift:=xlDown
             
     End If
     Next
           
    'Cells.Find(what:="total").Activate
    'ActiveCell.Offset(0, 1).Activate
    'x = ActiveCell.Column: y = ActiveCell.Row
               
End Sub

 

[Peter_SSs]

Care to show us a small set of dummy data and the expected results with XL2BB so that we can give it a try with a realistic layout and data?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[irfananeeza]

Thanks for your reply. Raw Sample Data

Sep 03	1	XYZ	ABC Corp
Sep 03	2	Irfan
Nov 01	3	AAA
Dec 03	4	Nothing
Feb 03	5	DEF
Jan 31	6	ABC
Apr 30	7	JKL
Jul 02	8	STU
Aug 04	9	VWX
Jan 02	10	ABC
Oct 03	11	BBC
Apr 01	12	JKL
Mar 31	13	GHI

After Macro Run Data

2-Jan	10	ABC	ABC Corp
31-Jan	6	ABC
Total
	ABC Corp
Date	Amount	Description
3-Feb	5	DEF
28-Feb	21	DEF
Total
	ABC Corp
Date	Amount	Description
3-Mar	17	GHI
31-Mar	13	GHI
Total

 

[Peter_SSs]

I'm struggling to replicate what you seem to be doing or to understand just what is going on.

When I copy the sample data from the first table in post 3 I get actual dates in the left column. There are two issues with this
- Your earlier explanation was that the dates were text
- The dates are coming to me as 1 September 2003 etc when I suspect that the date for you is 3 September ????

You say that your code converts the first column to dates. If so, the underlying value in the cell would be a number but then your code uses Left( ,2) on those values. It doesn't make sense to me.

So I'm not really sure how to progress.