Calculate according to proper size

Hello everyone
I have in sheet1 a number of products 1 , 2 , 3 in column B and each product consists of 4 items A , B , C and D in column C
In column D there are different sizes of each item
In row 1 in range ("F1:Q1") there are fixed sizes that I need to see the proper size for each item

* Note : the first item "A" is essential and should occur only at least for once
* The second item "B" would be 1 or 2 or 3 or more according to the available size in row 1
---------------------------------------------------------------------------------------
Examples :

1) As for the value in F1 is 1.500 - the expected result would be in range("F5:F8")
suppose A = 1 and B =1 >> so 1 * 0.280 + 1 * 0.600 = 0.880

Now we see the proper item (from "A" or "C" or "D") that will be close to 1.500 but it must be equal to 1.500 or smaller a little than this size
If we add 0.880 + item "A" (0.280) = 1.160
If we add 0.880 + item "C" (1.800) = 2.680
If we add 0.880 + item "D" (0.880) = 1.760
NOW let's the code determine the closest area of the three results 1.160 / 2.680 / 1.760 >>> the nearest one is 1.160

Finally the result in range("F5:F8") would be as following : F5 = 2 / F6 = 1 / F7 ="x" / F8 = "x" and F9 would have the remaining size which is calculated
(1.500 - 1.160) which will equal 0.340

--->> in summary the first item "A" is essential that should be 1 at least then the second item "B" would be flexible ( 1 / 2 / 3 or more according to
the available size ) then there must be one of the three items "A" or "C" or "D" according to the remaining size (the choice will be the nearest ..
equal to less than the fixed size in row 1)
2) Another example :
In range("J11:J15") :
In J14 will be "x" as there is no size related to item "D"
item "A" will be 1 which equals to 0.060
item "C" will be 1 which equals to 1.02
item "B" will be changeable to suit the desired size in J1 so I do it manually and reached 10 as 10 * 0.080 = 0.8
Now the total of the three items will be 1.880 (0.060 + 1.02 + 0.8)
In J15 will be the remaining which is 1.900 - 1.880 = 0.020


I know this is complicated issue but I hope to find help in this topic
Thanks advanced for help

* the thread is posted here too
http://www.eileenslounge.com/viewtopic.php?f=30&t=28955

* Sample file edited a little

Hello
Any help in this topic ..
We would start with just one example so as to find a starting idea

I have started this code but couldn't finish it yet. Can you help me

Sub Test()
    Dim r1 As Range
    Dim r2 As Range
    Dim x As Double
    Dim i As Long
    
    Set r1 = Range("D5:D8")
    Set r2 = Range("F5:F8")
    
    Range("F5").Value = 1
    
    Do
        Range("F6").Value = i + 1
        i = i + 1
        
        x = Evaluate("SUMPRODUCT(" & r1.Address & "," & r2.Address & ")")
        MsgBox x
        If x > Range("F1").Value Then Exit Do
    Loop Until Evaluate("F1-SUMPRODUCT(" & r1.Address & "," & r2.Address & ")") <= Application.Min(Range("F5").Value, Range("F7").Value, Range("F8").Value)
    
    Range("F9").Value = Evaluate("F1-SUMPRODUCT(" & r1.Address & "," & r2.Address & ")")
End Sub

This is a modified one but the result is not as expected

Sub Test()
    Dim r1 As Range
    Dim r2 As Range
    Dim x As Double
    Dim i As Long
    
    Set r1 = Range("D5:D8")
    Set r2 = Range("F5:F8")
    
    Range("F5").Value = 1
    
    Do
        Range("F6").Value = i + 1
        i = i + 1
        
        x = Evaluate("SUMPRODUCT(" & r1.Address & "," & r2.Address & ")")
        If x > Range("F1").Value Then Exit Do
    Loop Until Evaluate("F1-SUMPRODUCT(" & r1.Address & "," & r2.Address & ")") <= Application.Min(Range("D5").Value, Range("D7").Value, Range("D8").Value)
    
    Range("F9").Value = Evaluate("F1-SUMPRODUCT(" & r1.Address & "," & r2.Address & ")")
    If Range("F7") = "" Then Range("F7") = "x"
    If Range("F8") = "" Then Range("F8") = "x"
End Sub

Waiting for help from eperts

Any help in this topic please?

Can you give me an example of how to use solver here in this file .. I am confused about how to use the constraints

Thanks a lot for reply
I have looked at the file and that's great .. but I need t add more constraints if possible
Some basics first:

item "A" >> will be 1 or 2
item "B" >> will be 1 at least but may be more than 1 >> so it is >=1
item "C" >> will be 0 or 1
item "D" >> will be 0 or 1

item "B" should have at least 1 (this is a must) and may be more as I mentioned

After item "B" there should be two other items (according to the remaining available size) >> so the possibilities would be:
* item "A" = 2
* item "A" = 1 + item "C" = 1
* item "A" = 1 + item "D" = 1
that depends on the available nearest remaining size

Thanks a lot for reply. Forgive me as I have no great idea of using the solver so if possible can you attach a file with those constraints and I will study them through the file

Thank you very very much for great help. I began to learn a little about the solver and it is great
Now I have edited the Sheet2 example a little. Please have a look
I just want to the solver to skip D14 because it is empty. Is that possible or the solver doesn't manipulate the boolean values?

In K11 I have adjusted the formula in that way and worked well

=IF(D13="",SUM(J11,J14),IF(D14="",SUM(J11,J13),SUM(J11,J13,J14)))

** Last point .. Is that possible to convert those steps to a macro as I have a large sheet with the same processes?

Thanks a lot Mr. Alf
If you have navigated to the other thread you will find that we reached a very good point and you are the cause because you have adjusted the constraints in great way
Thank you very very much for your great and awesome help

This is the last solution at this link
http://www.eileenslounge.com/viewtop...224320#p224320

Just for the fun of it I extended the macro to cover all the rows and columns only difference was I used the "Simplex LP" engine to run solver.

Comparing the two results summing rows 9, 15 and 21 I got a total of 1.724 with the "Simplex LP" as opposed to 3.012 with the "GRG Nonlinear" engine.

To test run macro "SolvLoop"

Alf

Thank you very much for awesome support Mr. Alf
You are wonderful ..

Best Regards