I've looked all over and can't seem to figure out how to do this...

Cells A1:A100 have data
I want to have a button that when clicked, searches A1:A100
for the text in the current Active Cell.
Then, have it select each cell that matches.

Example: B1 has the word "blah"
B1 is selected.
A1:A100 have various words, but A5 and A8 have the word "blah"
I want to click on the button, and have A5 and A8 be selected as if
I had held CTRL and clicked on A5 and A8.

Here is one way to do it...

Sub SelectMatchingItems()
Dim R As Range
Dim SelectedItems As Range
For Each R In Range("A1:A100")
If R.Value = ActiveCell.Value And R.Value < "" Then
If SelectedItems Is Nothing Then
Set SelectedItems = R
Else
Set SelectedItems = Union(SelectedItems, R)
End If
End If
Next
If Not SelectedItems Is Nothing Then
SelectedItems.Select
End If
End Sub

Rick


"Christopher7291972" wrote in message
ps.com...
I've looked all over and can't seem to figure out how to do this...

Cells A1:A100 have data
I want to have a button that when clicked, searches A1:A100
for the text in the current Active Cell.
Then, have it select each cell that matches.

Example: B1 has the word "blah"
B1 is selected.
A1:A100 have various words, but A5 and A8 have the word "blah"
I want to click on the button, and have A5 and A8 be selected as if
I had held CTRL and clicked on A5 and A8.

Sub mySelector()
Dim myRange As Range, myCell As Range
Dim mySelection As String

Set myRange = Range("A1:A100")
mySelection = ""

For Each myCell In myRange
If ActiveCell.Value = myCell.Value Then
If Len(mySelection) < 0 Then
mySelection = mySelection & ","
End If
mySelection = mySelection & myCell.Address
End If
Next
Range(mySelection).Select
End Sub

--

Regards,
Nigel




"Christopher7291972" wrote in message
ps.com...
I've looked all over and can't seem to figure out how to do this...

Cells A1:A100 have data
I want to have a button that when clicked, searches A1:A100
for the text in the current Active Cell.
Then, have it select each cell that matches.

Example: B1 has the word "blah"
B1 is selected.
A1:A100 have various words, but A5 and A8 have the word "blah"
I want to click on the button, and have A5 and A8 be selected as if
I had held CTRL and clicked on A5 and A8.

That will fail when a match is not found because the string will be
empty, and will also fail when the string grows too long... The Union
approach is more robust.

-Basilisk96


"Nigel" wrote:
Sub mySelector()
Dim myRange As Range, myCell As Range
Dim mySelection As String

Set myRange = Range("A1:A100")
mySelection = ""

For Each myCell In myRange
If ActiveCell.Value = myCell.Value Then
If Len(mySelection) < 0 Then
mySelection = mySelection & ","
End If
mySelection = mySelection & myCell.Address
End If
Next
Range(mySelection).Select
End Sub

--

Regards,
Nigel