New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Macro to sum variable length range

Macros, VBA, Excel Automation, etc.

Macro to sum variable length range

Postby chiefsage » Mon Feb 09, 2015 8:49 pm

I'm at my wit's end trying to figure out how to create a macro that will perform a SUM on a variable length range of cells in a column.
    Imagine 5 rows in column A, starting at row 5.
    I can create a macro that will SUM that perfectly, regardless of which column it is in.
    But the macro will ALWAYS sum 5 rows. I cannot get it to recognize the number of values in the range and only SUM those values.

    Here's the code for my macro.

    Code: Select all
    Sub Macro22()
    '
    ' Macro22 Macro
    ' With Relative References turned on.
    '
    ' Keyboard Shortcut: Ctrl+o
    '
        ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    End Sub


    Any and all advice would be much appreciated.
    • 0

    chiefsage
    Rookie
     
    Posts: 3
    Joined: Feb 9, 2015
    Reputation: 0
    Excel Version: 2013

    Re: Macro to sum a variable length range

    Postby Don » Wed Feb 11, 2015 9:40 pm

    Hi and welcome to the forum!

    The formula that you show is a relative formula and it is hard-coded to do exactly what you stated. But you need to be more specific on how you want it to find values to sum. Does the Macro need to sum all cells that are above where you input the macro? If so, you could use this:

    Code: Select all
    Sub test()
    ActiveCell.Value = "=SUM(" & Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row - 1, ActiveCell.Column)).Address(False, False) & ")"
    End Sub
    • 0

    Don
    Moderator
     
    Posts: 733
    Joined: Dec 4, 2011
    Reputation: 2
    Excel Version: 2010

    Re: Macro to sum a variable length range

    Postby chiefsage » Thu Feb 12, 2015 9:40 pm

    Thank you for your response! It was close to what I needed. What I really need is for the Macro to recognize how many rows have values above it and only sum that range. If you look at the attached image, I need to be able to click in cells B4, B10, and B21 and execute the macro and have it only sum the values in that range.
    So for cell B4, I need the macro to know that it needs to sum cell B1 to B3.
    For cell B10, I need the macro to automatically know that it needs to sum the range of 4 cells from B6 to B9.
    For cell B21, I need the macro to automatically identify that the range to sum is now 9 rows (B12 to B20).

    Is that even possible? From what I've seen it should be possible, but it's super tricky to get the macro to do it.

    [img]
    Macro%20Example.png
    [/img]
    • 0

    You do not have the required permissions to view the files attached to this post.
    chiefsage
    Rookie
     
    Posts: 3
    Joined: Feb 9, 2015
    Reputation: 0
    Excel Version: 2013

    Re: Macro to sum a variable length range

    Postby chiefsage » Fri Feb 13, 2015 4:37 pm

    I obtained a solution from a different forum:

    Code: Select all
    Sub AutoSum()
        Const SourceRange = "C:F"
        Dim NumRange As Range, formulaCell As Range
        Dim SumAddr As String
        Dim c As Long

        For Each NumRange In Columns(SourceRange).SpecialCells(xlConstants, xlNumbers).Areas
            SumAddr = NumRange.Address(False, False)
            Set formulaCell = NumRange.Offset(NumRange.Count, 0).Resize(1, 1)
            formulaCell.Formula = "=SUM(" & SumAddr & ")"

            'change formatting to your liking:
            formulaCell.Font.Bold = True
            formulaCell.Font.Color = RGB(255, 0, 0)

            c = NumRange.Count
        Next NumRange

    End Sub


    This works on numbers in columns C to F, change that to any other range you want in the beginning of the code (constant SourceRange).

    Just make sure your areas of numbers don't touch each other.

    UPDATE: I wasn't aware of the rules of cross-posting. I posted the issue here first and didn't receive a helpful response for several days so I posted the same issue in another forum. The other forum solved my issue so I then posted the solution here to let people know that I no longer needed help. The link to the solution is below:
    http://superuser.com/questions/877603/need-an-excel-macro-to-sum-a-variable-length-range
    • 0

    Last edited by chiefsage on Sat Feb 14, 2015 11:43 am, edited 1 time in total.
    chiefsage
    Rookie
     
    Posts: 3
    Joined: Feb 9, 2015
    Reputation: 0
    Excel Version: 2013

    Re: Macro to sum a variable length range

    Postby pecoflyer » Sat Feb 14, 2015 3:18 am

    If I understand correctly you posted the same question on another forum without posting a link.

    Please read http://www.excelguru.ca/content.php?184 to understand why this behaviour is frowned upon by the peolple who give you free help on their spare time..
    • 0

    A relevant topic title helps get faster and more answers
    pecoflyer
    Moderator
     
    Posts: 1274
    Joined: Jan 24, 2012
    Location: Belgium
    Reputation: 39
    Excel Version: 2003/2007/2010

    Re: Macro to sum a variable length range

    Postby Alan » Thu Oct 06, 2016 5:07 pm

    I need either a new macro or make changes to this macro to do the following: Beginning at row 1 of column F and moving down, when you encounter a cell with a number (ex. F45 contains a value of 2), to sum that number plus all the contiguous numbers below (ex. F45:F47 say 2+3+4) until you encounter a blank cell (ex F48). Take that sum (9) and place it into the last blank cell before that range (F44). Then delete rows 45 through 47. Then continue to the end of the column doing the same process for each group of cells.

    Note: if this macro update works then it can be used to streamline QuickBooks Enterprise custom transaction detail reports for inventory adjustments.
    • 0

    Last edited by pecoflyer on Fri Oct 07, 2016 2:48 am, edited 1 time in total.
    Reason: Remove quote
    Alan
    Rookie
     
    Posts: 1
    Joined: Oct 6, 2016
    Reputation: 0
    Excel Version: 2010


    Return to Macros and VBA Questions

    Who is online

    Users browsing this forum: No registered users and 30 guests