VBA to Sum each variable range in a column separately

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
322
Office Version
  1. 2010
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.

VBA Code:
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
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,370
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 15, 2008
Messages
322
Office Version
  1. 2010
Thanks for your reply. Raw Sample Data
Sep 031XYZABC Corp
Sep 032Irfan
Nov 013AAA
Dec 034Nothing
Feb 035DEF
Jan 316ABC
Apr 307JKL
Jul 028STU
Aug 049VWX
Jan 0210ABC
Oct 0311BBC
Apr 0112JKL
Mar 3113GHI


After Macro Run Data
2-Jan10ABCABC Corp
31-Jan6ABC
Total
ABC Corp
DateAmountDescription
3-Feb5DEF
28-Feb21DEF
Total
ABC Corp
DateAmountDescription
3-Mar17GHI
31-Mar13GHI
Total
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,370
Office Version
  1. 365
Platform
  1. Windows
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. :confused:

So I'm not really sure how to progress. :(
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,482
Messages
5,770,351
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top