Seaching a Single Column without Selecting

How can I take the code listed below and do a search on a single column. In part of the sub I might want to seach Col B and in another part of the sub I might want to search Col J. What do I need to do to this so it will limit the search to a particular column.

Set wsEnroll = Workbooks("Enrollment Verifier DV.xls").Sheets("0708")
  
Set rData = wsData.Cells.Find(What:=sID, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

Thank you! I'll give that a try. There are some days it is easier to ask than search and test for an hour.

I appreciate the help.

This example should show you how to build what you want.
The routine looks through searchColumn looking for the string SearchFor.
They could be passed to your routine as arguments.

The address of the first cell is noted, so that the loop can test when .Find loops around to the top of the range again.

Notice that the Do Loop is inside With EndWith, so that searchColumn applies to .FindNext. Also note that .FindNext needs a starting point (the most recently found cell).

By avoiding selection, this can be run from when a differnent sheet is active

Sub test()
    Dim searchFor As String
    Dim searchColumn As Range
    
    Dim foundCell As Range
    Dim firstAddress As String
    
    Set searchColumn = ThisWorkbook.Sheets("sheet1").Range("A:A")
    searchFor = "a"
    
    With searchColumn
        Set foundCell = .Find(What:=searchFor, _
                        After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If foundCell Is Nothing Then MsgBox "not found": Exit Sub
        firstAddress = foundCell.Address
        
        Do
            Rem process foundCell
            MsgBox foundCell.Address
            
            Rem find next cell
            Set foundCell = .FindNext(After:=foundCell)
        Loop Until foundCell.Address = firstAddress
    
    End With
End Sub

mikerickson,

Set searchColumn = ThisWorkbook.Sheets("sheet1").Range("A:A")

Can the "A:A" of .Range("A:A") be set to a variable. I always seem to have trouble with that, but that is actually what I need. If it can be set to a variable, how do I do it.

searchColumn is a variable.

Having the Sub take arguments is one way to control it. Four lines would be removed and the declaration would be changed.
The start of the sub would look like

Sub FindStuff(searchFor as String, searchColumn As Range)
    'Dim searchFor As String: Rem remove
    'Dim searchColumn As Range: Rem remove

    Dim foundCell As Range
    Dim firstAddress As String

    'Set searchColumn = ThisWorkbook.Sheets("sheet1").Range("A:A"):Rem remove
    'searchFor = "a": Rem remove

     With searchColumn
        Set foundCell = .Find(What:=searchFor, _
                        After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Rem rest of code
End Sub

And then you could call the routine like

Sub MainRoutine()

Call FindStuff("Sam", ThisWorkbook.Sheets("Sheet1").Column("B"))

Call FindStuff("Dorothy", ThisWorkbook.Sheets("Sheet2").Range("D:E"))

End Sub

Note that all occurances of "Dorothy" is being found in both columns D and E. The search can be restricted to any continious range.

hi all,

Tushar's Findall function at the below link has a little more depth than what you are after but you may still find it useful...

http://www.tushar-mehta.com/excel/tips/findall.html

hth
Rob