Find and select all
 

How do I get this:
Cells.Find(What:="bilbobaggins", After:=ActiveCell
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False).Activate
To select all matching cells at the same time

--
rbmccle
-----------------------------------------------------------------------
rbmcclen's Profile: http://www.excelforum.com/member.php...fo&userid=3636
View this thread: http://www.excelforum.com/showthread.php?threadid=56159

Find and select all
 

Try this...

Sub FindAll()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wks As Worksheet
Dim rngToSearch As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngFound = rngToSearch.Find(What:="bilbobaggins", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry, Not found..."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
wks.Select
rngFoundAll.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"rbmcclen" wrote:


How do I get this:
Cells.Find(What:="bilbobaggins", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
To select all matching cells at the same time?


--
rbmcclen
------------------------------------------------------------------------
rbmcclen's Profile: http://www.excelforum.com/member.php...o&userid=36367
View this thread: http://www.excelforum.com/showthread...hreadid=561595

Find and select all
 

Exactly what I need, it works perfectly!

If you are ever in San Antonio I'll buy you a sod

--
rbmccle
-----------------------------------------------------------------------
rbmcclen's Profile: http://www.excelforum.com/member.php...fo&userid=3636
View this thread: http://www.excelforum.com/showthread.php?threadid=56159