I need code simply to activate all the cells found containing a text string
within a range of cells. For example, activate all cells containing
"Bioscience" in range A1:A350, including those that might say "Bioscience
Industry". Thanks!
--
Steve C

Only one cell can be active at any given instance.


"Steve C" wrote in message
...
I need code simply to activate all the cells found containing a text string
within a range of cells. For example, activate all cells containing
"Bioscience" in range A1:A350, including those that might say "Bioscience
Industry". Thanks!
--
Steve C

Assuming you mean Select when you say Activate, this code will select all
cells with Bioscience in them (although I'm willing to bet you don't really
need to select them in order to do whatever you plan to do next)...

Sub GetBioscienceCells()
Dim C As Range
Dim FoundCells As Range
Dim FirstAddress As String
With Worksheets("Sheet2").Range("A2:H200") '<== Set sheet & range here
Set C = .Find("Bioscience", LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If FoundCells Is Nothing Then
Set FoundCells = C
Else
Set FoundCells = Union(C, FoundCells)
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
If Not FoundCells Is Nothing Then FoundCells.Select
End If
End With
End Sub

--
Rick (MVP - Excel)


"Steve C" wrote in message
...
I need code simply to activate all the cells found containing a text string
within a range of cells. For example, activate all cells containing
"Bioscience" in range A1:A350, including those that might say "Bioscience
Industry". Thanks!
--
Steve C

you can use Conditional Formatting to highlight cells with specific vales,
or use autofilter to parse a table for specific values

"Steve C" wrote in message
...
I need code simply to activate all the cells found containing a text
string
within a range of cells. For example, activate all cells containing
"Bioscience" in range A1:A350, including those that might say "Bioscience
Industry". Thanks!
--
Steve C

Yes, I meant Select (my bad for poor choice of words). Your code does
exactly what I was looking for. Thanks to all who responded.
--
Steve C


"Rick Rothstein" wrote:

Assuming you mean Select when you say Activate, this code will select all
cells with Bioscience in them (although I'm willing to bet you don't really
need to select them in order to do whatever you plan to do next)...

Sub GetBioscienceCells()
Dim C As Range
Dim FoundCells As Range
Dim FirstAddress As String
With Worksheets("Sheet2").Range("A2:H200") '<== Set sheet & range here
Set C = .Find("Bioscience", LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If FoundCells Is Nothing Then
Set FoundCells = C
Else
Set FoundCells = Union(C, FoundCells)
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
If Not FoundCells Is Nothing Then FoundCells.Select
End If
End With
End Sub

--
Rick (MVP - Excel)


"Steve C" wrote in message
...
I need code simply to activate all the cells found containing a text string
within a range of cells. For example, activate all cells containing
"Bioscience" in range A1:A350, including those that might say "Bioscience
Industry". Thanks!
--
Steve C