VBA Find Function Error

[MarkRCC]

Hi All,

I have a userform that has a label that, when clicked, will take the user to the respective sheet and select the cell of what they have entered into a text field. Essentially just a automatic Ctrl+F button.

The code I am using works fine when the value exists but crashes when an non-existent value is entered. I knew this was going to be a problem, but I don't know what to add to the code to make it ignore/tell the user it was not found, rather than just crashing completely.

Private Sub Label3_Click()
ThisWorkbook.Sheets("HS").Activate
ActiveWorkbook.Worksheets("HS").Columns(1).Find(TextBox1.Value).Select
End Sub

How can I fix my problem? Thanks in advance.

 

[JoeMo]

One way to handle that run-time error is to find a cell (range object) with the value you want in it. If no such cell exists, the range object will be Nothing and your code can react to that. If a cell is found the code has set a range object variable to be that cell and you can do whatever you wish with it (e.g. select it). Like this:

Private Sub Label3_Click()
Dim FoundRng As Range
With Sheets("HS")
    Set FoundRng = .Columns(1).Find(textbox1.Value)
    If Not FoundRng Is Nothing Then
        .Activate
        FoundRng.Select
    Else
        MsgBox "Cannot find " & textbox1.Value & " in column A"
    End If
End With
End Sub