PDA

View Full Version : [SOLVED] VBA Sum a range of variable rows with a value below a set amount and loop



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

RyderS2
10-21-2015, 10:44 AM
Hi Graham...

What you would want to do is best to do in VBA... not with any cell functions... so let's look at what that might entail

You'd want to go down column B, adding values until >250

You'd want to remember what row you started in (startRow), and what row you finished in (endRow) Now you have a range of cells that represent what you need.

You would then set the column D value this way: Range("D" & endRow-1) = "=sum(B" & startRow & ":" & "B" & endRow -1 & ")"

Then continue by:
startRow = endRow

then repeating the process until you are done.

This will mean that you have to be comfortable in using loops and such in VBA. Are you comfortable with that?

R


This seems to work. It needs a single button named CommandButton1, and column B starting in row 6 onward needs some data.



Private Sub CommandButton1_Click()

Dim startRow As Integer
Dim endRow As Integer



startRow = 6
endRow = 6


Do
Total = 0
Do
If Trim(Range("B" & endRow)) <> "" Then
Total = Total + Range("B" & endRow)
endRow = endRow + 1
Else
GoTo GetOut
End If
Loop Until Total > 250
Range("D" & endRow - 2) = "=sum(B" & startRow & ":" & "B" & endRow - 2 & ")"
startRow = endRow - 1
endRow = endRow - 1
Loop


GetOut:


Range("D" & endRow - 1) = "=sum(B" & startRow & ":" & "B" & endRow - 1 & ")"


End Sub

gham1
10-22-2015, 07:02 AM
Hi Ryder,

Thanks so much for that - it was a massive help!

It was the startRow/endRow that was getting me - that's me well chuffed!

I did have to tweak your code slightly as it would not stop looping:

Sub Payingin_slips()


Dim startRow As Integer
Dim endRow As Integer

startRow = 6
endRow = 6

Do

Total = 0

Do

If Trim(Range("B" & endRow)) <> "" Then
Total = Total + Range("B" & endRow)
endRow = endRow + 1
Else
Range("D" & endRow - 1) = "=sum(B" & startRow & ":" & "B" & endRow - 1 & ")"
Range("E" & endRow - 1) = "=sum(C" & startRow & ":" & "C" & endRow - 1 & ")"
GoTo GetOut
End If

Loop Until Total > 250
Range("D" & endRow - 2) = "=sum(B" & startRow & ":" & "B" & endRow - 2 & ")"
Range("E" & endRow - 2) = "=sum(C" & startRow & ":" & "C" & endRow - 2 & ")"
startRow = endRow - 1
endRow = endRow - 1

Loop

GetOut:

End Sub


Thank you again