Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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 06:27.

    Excel Video Tutorials / Excel Dashboards Reports


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

    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 05:44.
    Triumph without peril brings no glory: Just try

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

    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 06:42. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Subtotals using Excel or VBA

    try
    Code:
    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
    Alsace France
    Posts
    3,958

    Re: Subtotals using Excel or VBA

    Perhaps a bit faster
    Code:
    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
    9,423

    Re: Subtotals using Excel or VBA

    try
    Code:
    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
    Code:
    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
    9,423

    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, 05:53
  2. Subtotals?
    By skycom in forum EXCEL HELP
    Replies: 5
    Last Post: November 20th, 2005, 15:30
  3. Subtotals
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: June 30th, 2005, 14:31
  4. subtotals
    By alexanderd in forum EXCEL HELP
    Replies: 3
    Last Post: May 5th, 2005, 05:53
  5. VBA : with subtotals
    By spnz in forum EXCEL HELP
    Replies: 1
    Last Post: March 24th, 2004, 04: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