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" <(E-Mail Removed)> wrote in message
news:B4DE4847-9487-4554-8FF4-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:B4DE4847-9487-4554-8FF4-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:B4DE4847-9487-4554-8FF4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:B4DE4847-9487-4554-8FF4-(E-Mail Removed)...
> >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
>
>