+ Reply to Thread
Results 1 to 36 of 36

VBA Sum Dynamic Range

  1. #1
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    VBA Sum Dynamic Range

    I am trying to sum a dynamic range. I want to look for the second non blank cell in a row range and sum all values in that range and then look through all specified columns. Thanks

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    The description of your problem is vague. You should post your workbook. It will help you get answers to your questions faster and provide you with more accurate solutions.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Basically, the code below is what I have so far. the first for statement will take the first non blank value in the row range and I am trying to get the second statement to sum all values in the row range after the first value. Currently, the second FOR statement will return the first correct value, but i want it to keep continuing and sum the rest of the values in the row range until the last non blank value. I know it is close but not sure how to add the sum formula to the code below.

    So in the brief sample below, I am trying to get it to sum 6 and 8, but not 3 (and then 2 and 3 and 4 but not 1)

    A B C d
    3 6 8
    1 2 3 4




    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    This macro code is quite different from yours. It relies on exploiting the properties and methods of a Range object to sum each row and transfer it to the destination. I have included comments to explain the macro. Let me know if you have any problems.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Thanks. However, the code did not work as I expected. I have attached a sample workbook. Column B is where I am trying to populate the values. In row 3 for example, I want it to look at cell E3, see that it has a value and then move to F3 and sum everything from F3 to X3. In row 10, I want it to sum everything from H10 to X10. all this will be based off a pivot table so the range will always be dynamic. Thanks in advance for the help.
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    I have most of the macro written. Could you provide me with a few more examples of rows to be summed? Two samples really isn't enough to confirm my test results.

  7. #7
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    I am not sure if I entered the code correctly, but it did not sum anything but simply inserted the first value in the cell. What should I enter in the source range if the source range is dynamic? Thanks

  8. #8
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    i think it is because there are blank cells in the range, it stops at a blank cell. If there are zeros, it continues. Is there a way to include blank cells until the second last value in the range. In the spreadsheet I attached, I am trying to sum all values after the first non blank value in the rows and have it stop before the grand total. Pre-money is the first non blank value and I have wirtten code that will extract this value. Post-money is the sum of all values after pre-money up until the grand total. Hope this makes more sense.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    That explanation helps. I do have couple of new questions. Is pre-money always the first date column? In your workbook this is column "E". Or is it the first non-empty cell in the row that is to be summed?

  10. #10
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    861

    Re: VBA Sum Dynamic Range

    Maybe:
    Please Login or Register  to view this content.
    Harry.

  11. #11
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Hello Leith: Correct, pre-monty is always the first non blank date column so it could start in any column. Post money is the sum of the range after pre-money. It includes every value after pre-money and should even include blanks in the sum and should stop summing before the grand total column. Also, the grand total column will not always be in col Y and is also dynamic.

    Hello HSV: Thanks for the code, unfortunately, when I entered this for the attached spreadsheet, it did not seem to do anything. Could you please clarify?

    Thanks

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    This macro is designed to handle a dynamic output as long as the following hold true:
    1) Row 2 is the header row.
    2) The dates in row 2 are contiguous cells.
    3) The data always starts in row 3.

    The macro code below has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Thanks Leith. This is very close.

    1) Row 2 will always be the header row
    2)the dates will be contiguous
    3)data will always start in row 3

    The only current issue is that there are some values that start in column M. Those values will be the pre-money and the others from N onwards will be the post-money. for values that start in H column, everything from I onwards will be the post money and will need to be summed. Currently, it works for values that start after column E but not for values that start in M. Thanks for your help

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    I am not really following you on the column "M" piece. Does this mean if everything from columns "E" to "K" are empty, and there are values in "L" and "M" then sum from "M" to the end?

  15. #15
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Yes, you are correct!

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    This change to macro should do it. The change is in red font...
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Thanks! I made the exact code change as you listed, however, that did not seem to change the results. Please advise. thanks

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    In that case, I need a few rows from the workbook where the errors occur and the expected results.

  19. #19
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    For example, in the below sample, only 1 and 8 should sum. The code works when there is a value in E column(For eg when 1 is seen, 2 and 3 will sum. but it is still not working when there are null values until L in the below sample. Hope this helps

    Please Login or Register  to view this content.

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    Sorry about that, there was a typo in the added code and the On Error statement hide it. The error was in the Set statement in the Else section. Range was misspelled as Rnage. The code should read...
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    thanks, it did work when those values started in the 12th columns as listed in the code above. What about if the start in the 11th or 10th or 9th or 8th or 20th or 25th so on and so forth. Can we set the 12 to a dynamic value, because it did not work for values that started in column G or Column H. It only worked for values that started in column E and column L. I have code to extract the first pre-money value. What if we look at the premoney value which will be in column A and then find it in the row and sum the range after it?

  22. #22
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    If I understand what you want then if a cell is not empty then start summing from 1 cell to the right of it till the end of the summing range. Would that be a correct assumption?

  23. #23
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Below is the code that I have to search for pre-money and return it in A column


    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Yes, you are correct!

  25. #25
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    This change should solve the problem. Here is the updated code in its entirety. As before, the change appears in red font.
    Please Login or Register  to view this content.

  26. #26
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    AAAHHHHH! I can't believe I forgot to exclude the first column dynamically! Here is the change to the If statement to ignore the first column in the Sum Range.
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    I think we are close. It did not work in a case where there were 2 columns with values, a few blank columns and then data. I have attached a sample workbook with the code and output values. I was referring to row 3,4 8,9 etc. (when it is in that format,the summing is incorrect.)Thanks
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    that code change fixed the first issue in my last post, but it undid the summing that was correct with the other rows/values. Thanks

  29. #29
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    I am confused. What are the summing rules again?

  30. #30
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    if a cell is not empty then start summing from 1 cell to the right of it till the end of the summing range. It should include null values and non null values.

  31. #31
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    Okay, this should be the final version. I have stepped through the macro and compared the results by summing the individual rows by hand. The macro has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  32. #32
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    861

    Re: VBA Sum Dynamic Range

    These conditions I kept me.

    Quote Originally Posted by rlsublime View Post
    In row 3 for example, I want it to look at cell E3, see that it has a value and then move to F3 and sum everything from F3 to X3. In row 10, I want it to sum everything from H10 to X10.
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Thanks Leith! This definitely solved it. One more related question regarding the code below. For i below, how would I replace the value 10 to instead find the
    last non blank value in column D and keep filling i till the end?Thanks


    Please Login or Register  to view this content.

  34. #34
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Sum Dynamic Range

    Hello rlsublime,

    The change below starts at the bottom of column "D" and moves up until it finds a cell with data. It then returns the row number for that cell.
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    11-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007, Excel 2003, Excel 2010
    Posts
    284

    Re: VBA Sum Dynamic Range

    Thanks! There is one more thing that I noticed. The last values in A and B column should be the sumof all the values. Right now it is simply returning the values based on the code. The code below selects the correct range, but how would I sum the selected range in the last cell with a value? Thanks

    Please Login or Register  to view this content.

  36. #36
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    861

    Re: VBA Sum Dynamic Range

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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