How to add cell value to another cell until a certain limit is reached

[ventstoy]

Hey people, I am trying to make a vba script where we have unknown number of rows and respective numbers which show how many products can one euro palette store. The idea is that I will start from the end of the list and start adding the last value to the one above until a certain number of products is reached (for example 50) in the first row and then continuing to the cell below the one which is already considered full. To get a better understanding lets say we have the following sheet:

column A
23
43
17
30

After executing the code this column will look the following:

column A
50
50
13

My idea is using Do While but I would really appreciate any help!

 

[Joe4]

I just noticed one small issue, the code clears the first cell, which in my case is the header. I tried to change the range that gets their content cleared to start from cell 2 but it shows me a problem. Would you be able to let me know how to fix that? Thank you!

Try this variation, to leave row 1 intact:

Sub MyMacro()

    Dim mySum As Long
    Dim myLim As Long
    Dim myCount As Long
    Dim myRm As Long
    
'   Set limit to put in each cell
    myLim = 50
    
    Application.ScreenUpdating = False
    
'   Calculate sum of column A
    mySum = Application.WorksheetFunction.Sum(Range("A:A"))
    
'   Calculate number of iterations of maximum to place in cells
    myCount = Int(mySum / myLim)
    
'   Calculate remainder
    myRm = mySum - (myCount * myLim)

'   Clear column A, except for row 1
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
'   Place in maximums
    If myCount > 0 Then Range("A2:A" & myCount + 1).Value = myLim
   
'   Place in remainder
    If myRm > 0 Then Range("A" & myCount + 2).Value = myRm
   
    Application.ScreenUpdating = True
    
End Sub

 

[ventstoy]

Try this variation, to leave row 1 intact:

Sub MyMacro()

    Dim mySum As Long
    Dim myLim As Long
    Dim myCount As Long
    Dim myRm As Long
   
'   Set limit to put in each cell
    myLim = 50
   
    Application.ScreenUpdating = False
   
'   Calculate sum of column A
    mySum = Application.WorksheetFunction.Sum(Range("A:A"))
   
'   Calculate number of iterations of maximum to place in cells
    myCount = Int(mySum / myLim)
   
'   Calculate remainder
    myRm = mySum - (myCount * myLim)

'   Clear column A, except for row 1
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
   
'   Place in maximums
    If myCount > 0 Then Range("A2:A" & myCount + 1).Value = myLim
  
'   Place in remainder
    If myRm > 0 Then Range("A" & myCount + 2).Value = myRm
  
    Application.ScreenUpdating = True
   
End Sub

Worked like a charm, amazing! Thank you so much!

 

[Peter_SSs]

I would really appreciate if you give me an explanation,

The formula adds everything in column A (down to row 1000 but you can make that any row you want so long as it will include all your numbers) which gives 113 then in
B2 checks that we have not already got 113 in column B then puts the smaller of original column A total and 50. In the example that is 50.
B3 checks that we have not already got 113 in column B then puts the smaller of original column A total (113) less the column B total so far (50) = 63 and 50. In the example that is 50 again
B4 checks that we have not already got 113 in column B then puts the smaller of original column A total (113) less the column B total so far (100) = 13 and 50. In the example that is 13
B5 checks that we have not already got 113 in column B & since we now do have that total puts ""
Same for all following rows.

 

[ventstoy]

The formula adds everything in column A (down to row 1000 but you can make that any row you want so long as it will include all your numbers) which gives 113 then in
B2 checks that we have not already got 113 in column B then puts the smaller of original column A total and 50. In the example that is 50.
B3 checks that we have not already got 113 in column B then puts the smaller of original column A total (113) less the column B total so far (50) = 63 and 50. In the example that is 50 again
B4 checks that we have not already got 113 in column B then puts the smaller of original column A total (113) less the column B total so far (100) = 13 and 50. In the example that is 13
B5 checks that we have not already got 113 in column B & since we now do have that total puts ""
Same for all following rows.

Thank you for the clear explanation Peter! Much appreciated.

 

[Peter_SSs]

No problem