VBA Help! - Really new to vba

[leeact]

Hi there!

I am currently working on a project where I would like a macro to go through a "trial and error" process to find a best fit solution with a few constraints. I am new to vba and would love some help, even if it's just where to start.

The project I am working on has 4 unknowns counts (#A, #B, #C, #D), and I am given 4 prices for those items, the sum of (#A + #B + #C + #D = 4) and the total cost should be would be (#A*PriceA+#B*PriceB+#C*PriceC+#D*PriceD = 214.83). Column I ("Actual cost") is using columns A - D to calculate the total cost. I would like to have a macro go through all possible combinations of #A #B #C #D to get to the desired cost of 214.83 in this case, with the constraints that (#A + #B + #C + #D = 4) and that #A, #B, #C, #D are all whole numbers. (The answer is #A = 1, #B = 1, #C = 1, #D = 1).

Col A | Col B | Col C | Col D | Col E |Col F | Col G | Col H | Col I | Col J | Col K |
#A |#B | #C | #D | PriceA | PriceB | PriceC | PriceD |Actual Cost| Desired Cost | Needed Total of A B C D|
3 |1 | 0 | 0 | 24.57 |46.21 |55.04 | 89.01 | 119.2 | 214.83 | 4 |


Any help would be GREATLY appreciated!

Thank You!

 

[StephenCrump]

Welcome to the Forum!

If you're happy with a brute force approach testing every possibility (which should work fine here given the very small number of possibilities) then you'll need to test quantities of 0, 1, .... 4 for #A, and then for each of these, quantities of 0, 1 .. 4 for #B ..., and for each of these ...... etc.

So you'll need 4 nested loops.

But in practice you'll only to test 0-3 for #C (because INT(214.83/55.04) is 3) and 0-2 (i.e. INT(214.83/89.01) for D.

Do you know how to write loops using the Next ... For Statement?

 

[leeact]

Thanks for the input!

I think that's what I had in mind, but im not even sure where to start with the code. Is there another way that this can be solved rather than a brute force approach? I ask because the actual project I am working on can have #A+#B+#C+#D = up to 50. Im maybe rethinking this approach if there is another way to do it. Any thoughts?

 

[StephenCrump]

Is there another way that this can be solved rather than a brute force approach? I ask because the actual project I am working on can have #A+#B+#C+#D = up to 50.

Small beer for VBA! Try this:

Sub Test()

    Dim a As Long, b As Long, c As Long, lCount As Long
    Dim lPrice(1 To 4) As Long, lTarget As Long, lToFill As Long, lTotal As Long
    
    For a = 1 To 4
        lPrice(a) = 100 * Range("B2:B5")(a).Value
    Next a
    lTotal = 50
    lTarget = 100 * Range("TargetPrice").Value
        
    For a = 0 To Int(lTarget / lPrice(1))
        lToFill = lTarget - a * lPrice(1)
        For b = 0 To Int(lToFill / lPrice(2))
            lToFill = lTarget - a * lPrice(1) - b * lPrice(2)
            For c = 0 To Int(lToFill / lPrice(3))
                If (lTotal - a - b - c) * lPrice(4) = lToFill Then
                    lCount = lCount + 1
                    MsgBox "Solution " & lCount & ": a=" & a & ", b=" & b & ", c=" & c & ", d=" & lToFill / lPrice(4)
                End If
                lToFill = lToFill - lPrice(3)
            Next c
        Next b
    Next a
    
    If lCount = 0 Then MsgBox "No Integer Solution found!"
    
End Sub

Excel 2010
	A	B
1	Number	Price
2	5	24.57
3	16	46.21
4	28	55.04
5	1	89.01
6	50	2,492.34
7
8	TargetSum	2,492.34
Sheet1

 

[hiker95]

I am new to vba and would love some help, even if it's just where to start.

leeact,

Welcome to the MrExcel forum.


See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 05/21/2017


See Reply #2 at the next link:

https://www.mrexcel.com/forum/excel-questions/1008151-preparing-job-interview-excel-test.html