Given the range A1:A1000, I'm trying to write some code that will look in the
values throughout the range, find all those cells that have the word "Test"
(or other critieria) in them, and then select all of these cells.

I have tried to use the Find function in excel to do this, and while I can
use this function to find all values in a given range, I'm not able to then
immediately select these same values. Thanks for your help.

try either datafilterautofilter
or look in the visual basic editor HELP for find. There is a good example of
findnext

--
Don Guillett
SalesAid Software

"Jason" wrote in message
...
Given the range A1:A1000, I'm trying to write some code that will look in
the
values throughout the range, find all those cells that have the word
"Test"
(or other critieria) in them, and then select all of these cells.

I have tried to use the Find function in excel to do this, and while I can
use this function to find all values in a given range, I'm not able to
then
immediately select these same values. Thanks for your help.

I copied the stuff from VBA's help and made some minor changes:

Option Explicit
Sub testme()

Dim myRng As Range
Dim FoundCell As Range
Dim AllCells As Range
Dim FirstAddress As String
Dim whatToFind As String

whatToFind = "test"

Set myRng = ActiveSheet.Range("a1:A1000")

With myRng
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
If AllCells Is Nothing Then
Set AllCells = FoundCell
Else
Set AllCells = Union(FoundCell, AllCells)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

If AllCells Is Nothing Then
MsgBox whatToFind & " wasn't found"
Else
AllCells.Select
End If

End Sub

Take a look at that .find line. You'll want to adjust it to match what you need
(xlwhole/xlpart, matchcase stuff).



Jason wrote:

Given the range A1:A1000, I'm trying to write some code that will look in the
values throughout the range, find all those cells that have the word "Test"
(or other critieria) in them, and then select all of these cells.

I have tried to use the Find function in excel to do this, and while I can
use this function to find all values in a given range, I'm not able to then
immediately select these same values. Thanks for your help.

--

Dave Peterson