Thread: Find value within (and only within) a range

Hi - haven't been able to find much on this ... but I'm sure one of you smart people know

In vba code, how can I restrict my "find" to a given range?

I'm dynamically building the range (anywhere from 5 - 100 cells in column B on my spreadsheet) and need to see if two values are present anywhere in that range. There will be multiple "ranges" to search, all in a single column.

I suppose I could loop through each range, but am trying to avoid extraneous loops if there's a better way to do it.

Thanks for any help!

try

Code:

Sub FindRng()
Dim Fnd As String
Dim Rng As Range
Fnd = "2"
    With Sheets("Sheet1").Range("A1:A10,A20:A30")
        Set Rng = .Find(What:=Fnd, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
End Sub

cheers