Fitting numbers into cell range

[BKGLTS]

Hello,

Is there a method/formula,function that would calculate the minimum numbers to add to a row range to equal a certain target value?

For example:

Say I have a ten cell range A1:A10.
I have a target value of 15.
I need the solution to fill as many cells, starting from A1, as it can with 1's and then switch to 2's and 3's and so on, with the goal of achieving the target number with the smallest number values.

So, the desired result would be:
A1=1

A2=1

A3=1
A4=1
A5=1
A6=2
A7=2
A8=2
A9=2
A10=2​

_{^{<strike>
</strike>}}​

The sum of all the cells equals 15, but uses the lowest numbers possible to achieve it.
Once the number increases it cannot decrease again.

Another example with target number of 17:

A1=1
A2=1

A3=1
A4=1
A5=1
A6=2
A7=2
A8=2
A9=3
A10=3​

​

Thank you!
B

 

[footoo]

Why would a target of 17 be like you posted instead of :

A1=1

A2=1

A3=1

A4=2

A5=2

A6=2

A7=2

A8=2

A9=2

A10=2 Or : A1=1 A2=1 A3=1 A4=1 A5=2 A6=2 A7=2 A8=2 A9=2 A10=3 <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[BKGLTS]

Your first example would be the desired result. You are correct! Thank you for that!

 

[footoo]

Enter the target value in B1 and :

Sub FT()
Dim rng As Range, targetN%, low%, r%
Set rng = [A1:A10]
targetN = [B1]
low = Int(targetN / rng.Count)
rng = low
For r = rng.Count To 1 Step -1
    If Application.WorksheetFunction.Sum(rng) = targetN Then Exit For
    rng(r) = low + 1
Next
End Sub