Excel Question

[Jjit]

There are three different slides sizes,
A=16, B=20, C=25 and “Q” is the customer size.
What would be the program for if we enter “Q” then it will give us Result for combinations for A, B, C?
Example,
If Q=16 then A=1, B=0, C=0 (as we don’t want ‘B’ and ‘C’ slides as one of ‘A’ size is 16.
If Q=61 then A=1, B=1, C=1.
If Q=86 then A=1, B=1 and C=2.
If Q=100 then A=0, B=5, C=0.
And also A=0, B=0, C=4.
What would be the program for if we enter value of Q so it give us combinations for A,B and C?
Thanks.

 

[tinab]

Hi, here's a program

In cell Sheet1(A1) type value of Q, combinations of A, B and C are in columns 1, 2 and 3 respectively

Sub combinations()
Dim Q, A, B, C, i, j, k, l, n As Integer
Q = Sheets("Sheet1").Cells(1, 1)
A = 16
B = 20
C = 25
l = 0
For i = 1 To Q
For j = 1 To Q
For k = 1 To Q
pom = (i - 1) * A + (j - 1) * B + (k - 1) * C
If pom = Q Then
l = l + 1
Sheets("Sheet1").Cells(l + 3, 1).Value = i - 1
Sheets("Sheet1").Cells(l + 3, 2).Value = j - 1
Sheets("Sheet1").Cells(l + 3, 3).Value = k - 1
End If
Next k
Next j
Next i
End Sub

You can try to make some n=int(Q/C) and make for i=1 to n and so on - it will take less time to do program if Q is large.

bye