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!

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you need vba?

22 05 11.xlsm
	A	B
1	Data	Result
2	23	50
3	43	50
4	17	13
5	30
6
7
ventstoy

Cell Formulas
Range	Formula
B2:B7	B2	=IF(SUM(B$1:B1)=SUM(A$2:A$1000),"",MIN(SUM(A$2:A$1000)-SUM(B$1:B1),50))

 

[Joe4]

Welcome to the Board!

Here is one way using VBA that requires no loops (which is usually preferred, as loops are generally slow):

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
    Range("A:A").ClearContents
    
'   Place in maximums
    If myCount > 0 Then Range("A1:A" & myCount).Value = myLim
   
'   Place in remainder
    If myRm > 0 Then Range("A" & myCount + 1).Value = myRm
   
    Application.ScreenUpdating = True
    
End Sub

 

[ventstoy]

First of all, many thanks for the fast reply Peter. Code works perfectly but can I ask what does it do, if you can explain me the syntax since I have problems understanding it. Basically, I am trying to automate a process thats why VBA would be preferred because I would like to show the results in a new sheet showing the updated quantity levels, rather than both old data and updated data.

 

[Joe4]

First of all, many thanks for the fast reply Peter. Code works perfectly but can I ask what does it do, if you can explain me the syntax since I have problems understanding it. Basically, I am trying to automate a process thats why VBA would be preferred because I would like to show the results in a new sheet showing the updated quantity levels, rather than both old data and updated data.

If you are looking for a VBA solution, did you try the code I posted?

 

[ventstoy]

If you are looking for a VBA solution, did you try the code I posted?

Yes and it worked perfectly fine, thank you so much! I hope my boss would be satisfied with the results?

 

[Peter_SSs]

VBA would be preferred

Yes and it worked perfectly fine,

So, if you are looking for vba and Joe's code does what you want, you don't need further explanation of my formula approach?

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[ventstoy]

If you are looking for a VBA solution, did you try the code I posted?

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!

 

[ventstoy]

So, if you are looking for vba and Joe's code does what you want, you don't need further explanation of my formula approach?

I would really appreciate if you give me an explanation, because it is a really good non VBA solution which I may use for the future.