Hi all,
This should hopefully be an easy one to solve but not for me.

I have a code which searches for a number in another workbook using the find method.

There are 2 problems:

1) When the user enters a value that doesn't exist in the workbook, i need it to find the next closest value, which it currently does not do.

2) The number values in the workbook it is searching, are formatted as strings and not numbers, I think this will need to be considered also.

Currently this code works perfect if the value exists, but if it doesn't exist it then throws an error.

Here is the code:

Sub BarrierStats()
    Dim wb     As Workbook
    Dim cl     As Range
    Dim R      As Long
    Dim sFind  As String
    Dim sFind2 As String
    Dim sFind3 As String
    
    R = ActiveCell.Row
    sFind = ThisWorkbook.Worksheets("Ratings History").Cells(R, 2).Value & "BS"
    sFind2 = ThisWorkbook.Worksheets("Ratings History").Cells(R, 3).Value
    sFind3 = ThisWorkbook.Worksheets("Ratings History").Cells(R, 15).Value
    Application.ScreenUpdating = False    ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Barrier Stats\" & sFind & ".xlsx", True, True)
    ' open the source workbook, read only
    With wb.Worksheets("Sheet1").Range("A1:Q16")
        ' read data from the source workbook
        Set cl = .Find(sFind2, LookIn:=xlValues)
        If Not cl Is Nothing Then
            Set cl = cl.Offset(1, 0)
            Set cl = Range(cl, cl.End(xlToRight)).Find(sFind3, LookIn:=xlValues)
            If Not cl Is Nothing Then
                ThisWorkbook.Worksheets("Ratings History").Cells(R, 28).Value = (cl.Offset(4, 0).Value + cl.Offset(5, 0).Value) / 2
            Else: MsgBox sFind2 & " not found"
            End If
        Else: MsgBox sFind & " not found"
        End If
    End With
    wb.Close False    ' close the source workbook without saving any changes
    Set wb = Nothing    ' free memory
    Set cl = Nothing
    Application.ScreenUpdating = True    ' turn on the screen updating
End Sub
I've highlighted the line which needs to be looked at, as sFind2 is the variable which needs to be found, and if not matched it needs to find the closest value.

I hope someone can help.

Yappa