Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Subtotals using Excel or VBA

  1. #1
    Join Date
    17th July 2012
    Posts
    3

    Subtotals using Excel or VBA

    Hello,

    I have been trying to calculate subtotals for a large amount of data in Microsoft Excel. On the spreadsheet, column J contains wind speed data for different months and years, with gaps in between. The gaps between each record are not regular however due to a lack of records for some months. I would like a formula on Excel or code for VBA that could somehow calculate the subtotal of the speed without me having to do it manually.

    The data I need to use is all in column J on Excel, and all the data is in numerical format. I have tried using SUMIF functions, but have not been able to crack a proper code yet! Any help/advice would be very much appreciated.

    If any more information is needed, let me know and I'll try and help.

    I've attached a copy similar to what my spreadsheet looks like. I can't really attach the whole thing as the spreadsheet is much too large and has sensitive information on it.

    I want to find a way for the totals to be calculated in each of the grey boxes next to SUM without me having to do it manually (for instance using AutoSum). The sums will be whenever there is an empty cell (or blank cell) between wind speed values, but sometimes there is more than one blank cell in a row.

    Thanks again in advancewind speed.xlsx
    Last edited by stevsh89; July 17th, 2012 at 05:27.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Subtotals using Excel or VBA

    "data is all in row J"
    Humm... curious letters are for columns.
    What a about a short sample of your data to prepare the right formula
    Last edited by PCI; July 17th, 2012 at 04:44.
    Triumph without peril brings no glory: Just try

  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Subtotals using Excel or VBA

    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.

    How to edit your post or thread title or upload an attachment

    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments -- use ZIP compression if necessary to meet file size limitations

    If you still cannot upload, then use a file share and provide the link.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    17th July 2012
    Posts
    3

    Re: Subtotals using Excel or VBA

    Thanks for that, I have uploaded a copy of my spreadsheet onto here with my original message
    Last edited by AAE; July 17th, 2012 at 05:42. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Subtotals using Excel or VBA

    try
    VB:
     
    Option Explicit 
     
    Sub SumPrepare() 
        Dim LastRow As Long 
        Dim I As Long, J As Long 
        Dim MySum As Long 
        Application.ScreenUpdating = False 
        LastRow = Range("I" & Rows.Count).End(xlUp).Row 
        For J = LastRow To 1 Step -1 
            If (Cells(J, "I") = "SUM:") Then 
                For I = J - 1 To 1 Step -1 
                    If ((Cells(I, "J") = "") Or (I = 1)) Then 
                        Cells(J, "J") = MySum 
                        MySum = 0 
                        Exit For 
                    Else 
                        MySum = MySum + Cells(I, "J") 
                    End If 
                Next I 
            End If 
        Next J 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  6. #6
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Subtotals using Excel or VBA

    Perhaps a bit faster
    VB:
    Sub SumPrepare2() 
        Dim LastRow As Long 
        Dim I As Long, J As Long 
        Dim MySum As Long 
        Application.ScreenUpdating = False 
        LastRow = Range("I" & Rows.Count).End(xlUp).Row 
        I = LastRow 
        For J = LastRow - 1 To 1 Step -1 
            If ((Cells(J, "I") = "SUM:") Or (J = 1)) Then 
                Cells(I, "J") = Evaluate("SUM(J" & J & ":J" & I & ")") 
                I = J 
            End If 
        Next J 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  7. #7
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,396

    Re: Subtotals using Excel or VBA

    try
    VB:
    Sub test() 
        Dim myAreas As Areas, myArea As Range 
        On Error Resume Next 
        Set myAreas = Columns("j").SpecialCells(-4123, 1).Areas 
        On Error Goto 0 
        If myAreas Is Nothing Then Exit Sub 
        For Each myArea In myAreas 
            With myArea 
                .Cells(.Cells.Count + 1).Formula = _ 
                "=subtotal(9," & .Address & ")" 
            End With 
        Next 
        Set myAreas = Nothing 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd July 2010
    Posts
    114

    Re: Subtotals using Excel or VBA

    try
    VB:
    Sub subtotals() 
        Dim c As Range, s As Variant 
        Set c = Range("J2") 
        Do 
            If Len(c(2)) = 0 Then 
                s = "x" & c.Address 
                Set c = c.End(4) 
            Else 
                s = "xsum(" & Range(c, c.End(4)).Address & ")" 
                Set c = c.End(4).End(4) 
            End If 
            c.End(3)(2) = s 
        Loop Until c.Row = Rows.Count 
        Range("J:J").Replace "x", "=" 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    17th July 2012
    Posts
    3

    Re: Subtotals using Excel or VBA

    Thanks to everyone for all of your suggested codes! Jindon, your one works the best on my spreadsheet so thanks again for doing that for me

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,396

    Re: Subtotals using Excel or VBA

    You are welcome.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Subtotals
    By scelston in forum EXCEL HELP
    Replies: 5
    Last Post: June 1st, 2011, 04:53
  2. Subtotals?
    By skycom in forum EXCEL HELP
    Replies: 5
    Last Post: November 20th, 2005, 14:30
  3. Subtotals
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: June 30th, 2005, 13:31
  4. subtotals
    By alexanderd in forum EXCEL HELP
    Replies: 3
    Last Post: May 5th, 2005, 04:53
  5. VBA : with subtotals
    By spnz in forum EXCEL HELP
    Replies: 1
    Last Post: March 24th, 2004, 03:29

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno