Are your gauge sizes really that random?

Here is a macro the might give you an idea for how to do this. I made some
assumptions about where data are on the worksheet, including the existence of
a named range "GageThicknesses" that contains a list of all the gages, in
order from smallest to largest. I linked the macro to a command button on
the sheet, but you can also use it by itself.

Modify to suit your needs.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim theRow, theCol
Dim nGauges As Long
Dim whichGauges() As Double
Dim targetGauge As Double
Dim totGauge As Double
'
ActiveSheet.Range("GaugeThicknesses").Select ' Named range listing gauges
nGauges = Selection.Rows.Count - 1 ' How many gauges are there?
theRow = Selection.Row ' Which row gauge list starts on
theCol = Selection.Column ' Which column gauge list starts on
'
' The target is two columns to the right of the gauge list
'
targetGauge = ActiveSheet.Cells(theRow + 1, theCol + 2)
'
totGauge = 0# ' Add gauges as we go
j = 0 ' Count number of gauges we add
For i = nGauges To 1 Step -1 ' Assume Gauges go small to large, go
backwards here
If (targetGauge - (totGauge + ActiveSheet.Cells(theRow + i, theCol))
-0.0001) Then
j = j + 1
ReDim Preserve whichGauges(j) ' Store gauges that add to total
whichGauges(j) = ActiveSheet.Cells(theRow + i, theCol)
totGauge = totGauge + whichGauges(j) ' Keep track of total
gauge thickness
End If
Next i
'
' We have all the Gauges the will fit within "targetGauge",
' now place them on the worksheet.
' NOTE: This does not guarantee an exact match if you
' don't have the right gauge thicknesses available!
'
For i = 1 To j
ActiveSheet.Cells(theRow + i, theCol + 4) = whichGauges(i)
Next i
'
End Sub


"emm" wrote:

I would like to create a formula or a macro that will display a list of
gauges to use to reach a specific size.
I have a table with gauge sizes.
I want to enter a size in a cell and have another cell(s) list the smallest
number of different gauges that I need to make that size.
For example: The table may contain 3, 3.5, 3.2. 2.7, 0.4, 0.2 and etc. If
I enter 3.4 in the size cell, then 3.2 and .2 appear in the list.

Any suggestions are appreciated,
Emm