+ Reply to Thread
Results 1 to 8 of 8

Sum of irregular ranges with macro

  1. #1
    Forum Contributor
    Join Date
    02-13-2017
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    102

    Sum of irregular ranges with macro

    Hi all,

    I would like a macro that gives me the sum of a range in a list.
    See yellow cells for the desired outcome of the macro.
    (formula doesn't has to be in the cell, result is enough)

    Problem is: the range is irregular (I mean: sometimes 2 values , sometimes 3, sometimes 16 ...)

    Excel attached.

    thx in advance
    Attached Files Attached Files

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Sum of irregular ranges with macro

    Hi Jonas,

    Try below code based on your sample file ...
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sum of irregular ranges with macro

    G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I'm assuming you could just copy G2 and paste into G6, G9, G13, G17, G20, G24, G27 and G30.

    As for a macro to do this, since cell A1 is blank, and you want totals on the same rows as nonblank cells in column A,

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: Sum of irregular ranges with macro

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Sum of irregular ranges with macro

    Quote Originally Posted by ikboy View Post
    Please Login or Register  to view this content.
    You could "simplify" your For statement by replacing [f1048576].End(3) with Rows.Count (you won't lose any speed by doing so).

  6. #6
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: Sum of irregular ranges with macro

    Thanks Rick Rothstein,
    My code have something wrong, it should changes as :
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sum of irregular ranges with macro

    Quote Originally Posted by nankw83 View Post
    . . .
    Please Login or Register  to view this content.
    . . .
    Why not just Application.Sum(c) ?

  8. #8
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Sum of irregular ranges with macro

    Quote Originally Posted by hrlngrv View Post
    Why not just Application.Sum(c) ?

    I was writing the code in a different way then change it & left this piece unchanged but you're right, it would be simpler this way

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to draw Irregular Polygon
    By gaului in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2017, 11:19 AM
  2. Replies: 4
    Last Post: 10-21-2015, 08:59 PM
  3. vba application acting irregular
    By kmakjop in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2012, 12:49 PM
  4. Three criteria irregular lookups
    By BRISBANEBOB in forum Excel General
    Replies: 10
    Last Post: 09-14-2009, 10:16 PM
  5. Irregular Macro Performance
    By lostit in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 07-23-2009, 04:17 PM
  6. Irregular hours
    By Fedde in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2007, 07:34 PM
  7. Replies: 2
    Last Post: 06-01-2006, 03:55 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1