Selecting all cells with specific text

[screech]

I would like help on some VBA code -- maybe it's too early in the morning but I can't figure it out.

I would like to select every cell on a worksheet that has a particular string of text in the cell. The text I'm looking for is "Rec". It will not be the only text in the cell. I keep trying to use the Find function in my code but I don't think this is the best way to do this. Please help.

 

[Von Pookie]

Do you have to use a macro? You can do this easily with the normal Find function in Excel (at least I can do it with 2002).

- Press Ctrl+F to bring up the Find dialog, and enter your search term.
- Click the Options button to display the rest of the dialog box
- Apply the following settings:
- Match Case and Match Entire Cell Contents are not checked
- Lookin: Values[/list]
- Click Find All and a list of all of the cells where the search term is found should be displayed underneath the Find dialog.
- By default, the first item in the list will be selected, and this will also select that cell on the worksheet. Just press Ctrl+A to select all of the items in the list, and this will also select all of those cells on the worksheet.
- Click Close to close the Find dialog box and retain the selected cells.

 

[screech]

Do you have to use a macro? You can do this easily with the normal Find function in Excel (at least I can do it with 2002).

I am using Excel 2000. I do not think I have that option in my Find command. And yes, I would like to have a macro do it automatically as there are several conditions like this that I would like it to check for every time.

 

[Von Pookie]

Excel 2000 should have a "Find All" button on the Find dialog, I'm pretty sure.

Anyway--a macro. This seems to work for me:

Sub test()
Dim c As Range, FoundCells As Range
Dim firstaddress As String

Application.ScreenUpdating = False
With Sheets("Sheet1")
    'find first cell that contains "rec"
    Set c = .Cells.Find(What:="rec", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:= _
    xlPart, MatchCase:=False)
    
    'if the search returns a cell
    If Not c Is Nothing Then
        'note the address of first cell found
        firstaddress = c.Address
        Do
            'FoundCells is the variable that will refer to all of the
            'cells that are returned in the search
            If FoundCells Is Nothing Then
                Set FoundCells = c
            Else
                Set FoundCells = Union(c, FoundCells)
            End If
            'find the next instance of "rec"
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
                
        'after entire sheet searched, select all found cells
        FoundCells.Select
    Else
        'if no cells were found in search, display msg
        MsgBox "No cells found."
    End If
End With
Application.ScreenUpdating = True

End Sub

 

[screech]

Excel 2000 should have a "Find All" button on the Find dialog, I'm pretty sure.

I don't see a Find All option on the Find Dialog... probably why I couldn't figure this out by using the Find command. But regardless, I did try your code and it works perfectly! Thank you very much for all of your help! I sincerely appreciate it.

 

[screech]

Hello again,

The code did work perfectly, but I am trying to make a small change to it. The original code searched the entire sheet for the text and added it to the range. I would like to not apply the search to Columns A to H. To make this change, I am trying to confine the search to Columns I through IV. The code that I am using is below but it still adds cells that are in columns A through H. How do I modify this code to tell it where I would like it to search?

Dim d As Range, dFoundCells As Range
Dim dfirstaddress As String
Columns("I:IV").Select
Set d = Selection.Find(what:="rec", After:=Selection(Rows.Count, 1), LookIn:=xlValues, LookAt:= xlPart, MatchCase:=False)

Thanks!

 

[screech]

Is there anyone that can help me restrict the range of cells that this acts on? I'll keep experimenting in the meanwhile...

 

[Fergus]

In fifth line of Kristy's code try replacing

With Sheets("Sheet1")

with

With Sheets("Sheet1").Range("I:IV")

Edit : corrected Range from I:VI to I:IV