|Right click the sheet where you have your table.|
Select View Code.
Copy and Paste this code into the window that opens.
I've modified it so that it runs as soon as you make a change to C1.
Enter a number in C1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTarget, myTotal, myRemainder
Dim myWeight As Integer
Dim myCombo As String
'Was change made to C1?
If Target.Address = "$C$1" Then
'Is there a number in C1
If Not WorksheetFunction.IsNumber(Cells(1, 3)) Then
MsgBox "C1 must contain a number"
'Grab Target Weight from C1
myTarget = Cells(1, 3)
'Loop backwards through weights in B1:B8
For myWeight = 8 To 1 Step -1
'If weight in cell is bigger than the target
'weight value, don't use it
If Cells(myWeight, 2) > myTarget Then GoTo tooMuch
'If weight in cell is less than than the target weight value,
'value, grab the associated label and concatenate it with
'the other labels
myCombo = myCombo & " " & Cells(myWeight, 1)
'Reduce target value by weight just used (set new target value)
myTarget = myTarget - Cells(myWeight, 2)
'Keep track of weights used
myTotal = myTotal + Cells(myWeight, 2)
'Loop and find next usable weight
'Strip leading space from combination and put it in D1
Cells(1, 4) = Right(myCombo, Len(myCombo) - 1)
'Calculate remainder and put it in E1
myRemainder = Cells(1, 3) - myTotal
Cells(1, 5) = myRemainder