Distribute specific number to categories

[YasserKhalil]

Hello experts
I have a code in worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Amount As Double, I As Integer
    If Target.Address = "$B$2" Then
        Amount = Range("B2").Value
        Range("B5:B16").Value = ""
        For I = 5 To 8
            Do While Amount >= Cells(I, 1).Value
                Cells(I, 2).Value = Cells(I, 2).Value + 1
                Amount = Amount - Cells(I, 1).Value
            Loop
        Next I
    End If
End Sub

I type a number in B2 and it is supposed to distribute this number to specific categories
If I typed 1.45 I got 1 in category 1 and 2 in category 0.2

The expected output is 1 in category 1 & 3 in category 0.15
Thanks advanced for help

 

[NARAYANK991]

Hi ,

This can be more complicated than it seems.

Can you confirm the following ?

1. How many items will be present in column A i.e. Category ?

Will it be only the 4 values that you have shown in your workbook , or can it increase ?

2. How many category values will be integer values ?

3. How many category values will be decimal values ?

4. Will the category values always be entered in descending order ?

Narayan

 

[YasserKhalil]

Thanks a lot Mr. NARAYAN for reply
As for the number of categories may be increased ..and as for categories are of integers and decimals (mixed)
And yes the category values will be always entered in descending order
Thanks a lot for advanced help

 

[Hui]

This is going to be a lot more difficult that you imagine

Firstly, I would add a 2 lines of code and change one as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Amount As Double, I As Integer
  Application.EnableEvents = False 'New
  If Target.Address = "$B$2" Then
  Amount = Range("B2").Value
  Range("B5:B16").Value = ""
  For I = 5 To 8
  Do While Amount >= Cells(I, 1).Value
  Cells(I, 2).Value = Cells(I, 2).Value + 1
  Amount = Int(100 * Amount - 100 * Cells(I, 1).Value) / 100 'Changed
  Loop
  Next I
  End If
  Application.EnableEvents = True 'New
End Sub

I also suspect that the data should be presented as


If that is correct then, the correct answer is:

 

[YasserKhalil]

Thanks a lot Mr. Hui for reply
In fact I need to deal with this situation as it is ..
As an idea is it possible to do that task using formulas .. may be it is easier doing that with formulas even if with helper columns

 

[NARAYANK991]

Hi ,

It will help if you can upload your working file , with all the possible categories , and specifying the minimum and maximum limits for the entered value in B2.

Narayan

 

[YasserKhalil]

Thanks Mr. NARAYAN
Please work on this sample ..
I don't know what do you mean with minimum and maximum limits ...but say that I can enter numbers to 1000 ..

 

[Hui]

The issue is that the larger the Value in B2 is the more combinations of smaller values can be generated to meet the criteria.

What is the ultimate criteria?

I assume it is to have no left over or remainder amount?
or is it to have minimal number of Categories or something else?

 

[Hui]

Can I also ask is this question about distributing money/notes & coins to match a total or is it something else?