Find with a tolerance

Hi guys,

Im looking to be able to enter a number and a tolerance to find other numbers similar .

eg, I search for "10" with a tolerance of "1", then it will find any number bewteen 9 and 11 eg (9.57899, etc), Similar to Ctrl+F, does anyone have any ideas how to do this?

Thanks,

Jimmy

Hi,

Consider using one of the filtering options. i.e. either an Autofilter or an Advanced Filter to extract the numbers required.

Hello JimmyWilliams,

The comment made by Richard Buttrey got me thinking about yet another automated approach.

Essence of the Code for a Standard Module;

Option Explicit
Sub TogRows()
Dim BeginRow As Integer, EndRow As Integer, ChkCol As Integer, RowChk As Long

    BeginRow = 3
    EndRow = 500  'Change end Row to suit
    ChkCol = 1  'Checks Column A. Change to suit

    Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
    
    For RowChk = BeginRow To EndRow
        If Range("C2") = "" Then Exit Sub  'Change check Cell to suit
        If Cells(RowChk, ChkCol).Value = Range("C2").Value + 1 _
        Or Cells(RowChk, ChkCol).Value = Range("C2").Value - 1 _
        Or Cells(RowChk, ChkCol).Value = Range("C2").Value Then
            Cells(RowChk, ChkCol).Rows.Hidden = False
        Else
            Cells(RowChk, ChkCol).Rows.Hidden = True
        End If
    Next RowChk
    
    Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
    
End Sub
Sub Reset()

Columns(1).Rows.Hidden = False

End Sub

In the attached sample I am giving you two options to choose from.Select Tab "Hide Rows" or Tab "Color Cells"

Regards.

If your values are in a single column of contiguous values then with the active cell somewhere in that column the following macro will autofilter the data.

Sub FindValues()
    Dim lTol As Double, lStart As Double
    On Error GoTo norange
    ActiveCell.CurrentRegion.AutoFilter
    lTol = InputBox("Input the tolerance")

    lStart = InputBox("Input start number")

    ActiveCell.CurrentRegion.Resize(, 1).AutoFilter , field:=1, Criteria1:=">=" & (lStart - lTol), _
                                                    Operator:=xlAnd, Criteria2:="<=" & (lStart + lTol)
    Exit Sub
norange:
    MsgBox "The active cell is not within a column range"

End Sub

Hello JimmyWilliams,

Following up from Post# 3, I have adjusted the Code a little and amended the sample Workbook accordingly. I think you may just like it.

Regards.