VBA to find a specified cell value in specified range and select it

Question

I am having trouble creating a macro that will find a specified value in my active sheet within a range in my "Information" sheet. If the cell value us not found in the range then it will give me a message box stating "Value not Found" I have the following but it is not working:

Sub testrot()
    Dim i As String
    Dim srchrng As Range

    Sheets(ActiveSheet.Name).Select
    i = Cells(2, 5)
    Sheets("Information").Select
    Set srchrng = Range("j8:j17").Find(what = i)
    If Not srchrng Is Nothing Then
        MsgBox "Not Found"
    End If
End Sub

The cell (2,5) in my active sheet is for example #16, and in the range (j8:j17) it is a list of different strings #16, #17, etc.

I appreciate any advice.

Thanks.

BruceWayne BruceWayne · Accepted Answer · 2016-10-13T22:00:44

You want to avoid .Select/.Activate

Sub testRot2()
Dim str As String
Dim srchRng As Range
With Worksheets(ActiveSheet.Name)
    str = .Cells(2, 5).Value
    Set srchRng = .Range("J8:J17").Find(what:=str)
    If srchRng Is Nothing Then
        MsgBox "Not found"
    End If
End With
End Sub

Also, note that I changed i to str. This is a personal choice, as I typically see i as a Long/Integer for looping. You can keep it i though, just thought to mention it. Also, note the colon required in Find(what:=str).

VBA to find a specified cell value in specified range and select it

1 Answers