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
Bookmarks