I'm asking myself what the desired values without rounding should be.
I think :
the desired thickness = actual thickness - actual clearance + (min clearance + max clearance)/2
the tolerance = (max clearance - min clearance)/2
so if another valve is in this range then it' ok, are is this assumption wrong ?
this macro isn't finished yet
Sub SwapMyValves()
Dim a, c As Range, Scal As Object, Dict As Object, i1%, i2%, delta As Double
Set Dict = CreateObject("scripting.dictionary")
With Dict
For Each c In Sheets("sheet1").Range("A7,A20").EntireRow.SpecialCells(xlConstants) 'my valve names
If c.Column > 2 Then
If c.Offset(2).Value = "OUT Of SPEC" Then
.Item(c.Value) = Array(c.Value, c.Offset(7).Value, c.Offset(7).Value - c.Offset(1).Value + 0.5 * (c.Offset(4).Value + c.Offset(5).Value), 0.5 * (c.Offset(5).Value - c.Offset(4).Value)) 'array with valve name, actual shim tickness, desired tickniss
End If
End If
Next
a = Application.Transpose(Application.Transpose(Application.Index(.items, 0, 0)))
End With
Set Scal = CreateObject("system.collections.arraylist")
With Scal
For i1 = 1 To UBound(a) 'loop for the new shim
For i2 = 1 To UBound(a) 'loop for the desired shim
delta = Abs(a(i1, 2) - a(i2, 3)) + Rnd / 100000 'difference between new shim and desired shim
If delta <= a(i1, 4) Or 1 Then 'within tolerance
.Add Join$(Array(Format(delta, "00.000000000"), a(i1, 1), a(i1, 2), a(i2, 1), a(i2, 3)), "|")
End If
Next
Next
.Sort
a = .toarray
End With
With Sheets("sheet2").Range("A1").Resize(UBound(a))
.Value = Application.Transpose(a)
Application.DisplayAlerts = False
.TextToColumns other:=1, otherchar:="|"
End With
End Sub
Bookmarks