gham1
10-21-2015, 03:58 AM
Hi,
I have recently been on a course to increase my limited knowledge of VBA so that I can start doing more magic and wonderful things with Excel 2013 and I have undertaken my first real project where some real coding is involved. All has gone well and I have successfully managed to create a number of reports from a poor extract from our system data; all except for the final hurdle (the last part of the last report) which I discovered was lurking in the depths of Excel hell. I was just wondering if someone would maybe be able to give me a hand and help drag me back out again!
I have put a picture below to try and demonstrate exactly I am wanting to achieve; this table is created by another macro once various adjustments have been made.
14620
I have manually entered the formulas in columns D and E - however I want to create a formula that will do this for me - the ranges however would be variable as the number of batches and cheques to a batch will always change. You can bank up to 250 cheques on a paying in slip and I therefore want to lump batches of cheques together (column B is the number of cheques in a batch) that fall within the 250. So as you will see I have highlighted a range of cells 1 row longer than the 1st sum formula in column D which has a sum of 265 so it passes the threshold. I would therefore want the macro to sum the range preceding that row. I would then want it to continue to do this until it gets to the bottom of the data while excluding any previously summed ranges.
I know how to sum ranges and use the offset function to put a sum formula where I want it and I know that it will need a loop function to perform the task and given that the table has sum formulas for the columns at the bottom I guess it would need a loop until with an offset cell + 2 select below the selected range is blank/"" for it to stop but I am struggling to string it all together and put it into something workable/that I can test.
I've about googled as many variants as what I can think of to try and get some aid in what I am trying to achieve but I am really struggling. Any help or advice would be absolutely massively appreciated!
Thanks.
Graham
I have recently been on a course to increase my limited knowledge of VBA so that I can start doing more magic and wonderful things with Excel 2013 and I have undertaken my first real project where some real coding is involved. All has gone well and I have successfully managed to create a number of reports from a poor extract from our system data; all except for the final hurdle (the last part of the last report) which I discovered was lurking in the depths of Excel hell. I was just wondering if someone would maybe be able to give me a hand and help drag me back out again!
I have put a picture below to try and demonstrate exactly I am wanting to achieve; this table is created by another macro once various adjustments have been made.
14620
I have manually entered the formulas in columns D and E - however I want to create a formula that will do this for me - the ranges however would be variable as the number of batches and cheques to a batch will always change. You can bank up to 250 cheques on a paying in slip and I therefore want to lump batches of cheques together (column B is the number of cheques in a batch) that fall within the 250. So as you will see I have highlighted a range of cells 1 row longer than the 1st sum formula in column D which has a sum of 265 so it passes the threshold. I would therefore want the macro to sum the range preceding that row. I would then want it to continue to do this until it gets to the bottom of the data while excluding any previously summed ranges.
I know how to sum ranges and use the offset function to put a sum formula where I want it and I know that it will need a loop function to perform the task and given that the table has sum formulas for the columns at the bottom I guess it would need a loop until with an offset cell + 2 select below the selected range is blank/"" for it to stop but I am struggling to string it all together and put it into something workable/that I can test.
I've about googled as many variants as what I can think of to try and get some aid in what I am trying to achieve but I am really struggling. Any help or advice would be absolutely massively appreciated!
Thanks.
Graham