Pulling non zero values from excel

Question

i have made this table in excel of items of food from a menu with their nutritional value. I've then got another table that has each item in one row and the quantity consumed of each item In the row below. I've then used the solver tool in excel to optimise the meal choices with set constraints on the amount of calories a meal can be as well as certain restrictions on the nutritional values that I have used.

When I run the simplex algorithm through the solver tool what happens is the values in the table for quantity consumed changes to reflect what you should eat given the constraints.

I want to make it flexible so that I can change the constraints and get different results but what I want is an easy way to show the choices made. Currently what I have is an index match on another tab to table and the values which I then apply a filter to and take off all the items with '0' for quantity consumed however this has to be done each time I run the solver.

Is there any way to pull the non zeros and display what item these refer to without having to redo the filter every time ?

Answer 1

Here's a simple routine I use to look up something in a spreadsheet table and post the results on the same page (easily changed to post on another sheet). Hope this helps or heads you in the right direction. (Crude but effective)

Private Sub CommandButton3_Click()
    'FIND A VALUE IN THE LIST AND POST RESULTS

Dim myName, myNumber, myComp

  'Clear the results area
With Worksheets("SheetName").Range("H2:J30").ClearContents
End With
x = 2   'The row to start posting results to
y = 0

  'This is the range to search
With Worksheets("SheetName").Range("A1:D300")

   Set found = .Find(What:=y, LookIn:=xlValues,  LookAt:=xlWhole)
      If Not found Is Nothing Then
        firstAddress = found.Address
      Do

        Set ws = Sheets("SheetName")
        myName = ws.Range("A" & found.Row).Value  'Value in column A
        myNumber = ws.Range("B" & found.Row).Value 'Value in column B
        myComp = ws.Range("C" & found.Row).Value   'Value in column C 

               'I use a MsgBox at first for testing, then comment it out  when I know it's working 
               'MsgBox myName & "   " & myNumber & "    " & myComp

                'Post the results to the desired area
                ws.Range("H" & x).Value = myName
                ws.Range("I" & x).Value = myNumber
                ws.Range("J" & x).Value = myComp
            x = x + 1
        Set found = .FindNext(found)
        If found Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While Not found Is Nothing And found.Address <> firstAddress
   End If
DoneFinding:
End With
Range("A2").Select
End Sub