Set range based on find

[Jason Morin]

Greetings! Through VBA, I'd like to set a range where each
cell in the range contains a value I'm looking for. For
example, assume columns A and B have text values like:

A1: "a,b,c,e"
A2: "d,k,l,m,n"
A3: "b,c,j,t"
B1: "d,e"
etc.

I'd like to loop through each cell in the 2 columns,
looking for "b". If a cell contains "b", then it becomes
part of my new range (call it "rng"). In this case,

Set rng = Range("A1","A3")

Thanks a million!

Jason

 

[Tom Ogilvy]

Sub Findall()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Cells
Set rng1 = rng.Find("b", After:=Range("IV65536"), _
LookIn:=xlValues, Lookat:=xlPart)
If Not rng1 Is Nothing Then
fAddr = rng1.Address
Do
If rng2 Is Nothing Then
Set rng2 = rng1
Else
Set rng2 = Union(rng2, rng1)
End If
Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> fAddr
End If
If Not rng2 Is Nothing Then
rng2.Select
End If
End Sub

 

[Tom Ogilvy]

If not obvious, then
for just columns A and B, you would change
Set rng = Cells
to
Set rng = Range("A:B")