Searching for specific case

[okrob]

Need to ONLY select cells with 2 spaces then a character other than a
space. I have cells in the column that start with one space, some
start with 3 or more spaces. I only want to select the cells that
have 2 spaces out front.

Something like Ron's code - See Below - selects all the cells with 2
spaces in them regardless of the location of the spaces.
I figure using a Left(" ",2) statement in conjunction with something
that tells it to look for the 3rd character and if it's a space, skip
it.

Hope I've given enough info for someone to help.
Thanks,
Rob


Sub Union_in_column()
Dim FirstAddress As String
Dim str As String
Dim rng As Range
Dim rng2 As Range
str = " " '<===== This is where I need help.
With Range("C:C")
Set rng = .Find(What:=str, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
If rng2 Is Nothing Then
Set rng2 = rng
Else
Set rng2 = Application.Union(rng2, rng)
End If
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <>
FirstAddress
End If
End With
If Not rng2 Is Nothing Then rng2.Select
End Sub

 

[okrob]

Nevermind...

=IF(LEFT(C1,2)=" ",IF(MID(C1,3,1)<>" ",TRUE,FALSE),FALSE)

 

[Tom Ogilvy]

str is a function. don't use it as a variable name. Note I also change
xlPart to xlWhole

Sub Union_in_column()
Dim FirstAddress As String
Dim sStr As String
Dim rng As Range
Dim rng2 As Range
sStr = " *" ' two spaces and an asterick
With Range("C:C")
Set rng = .Find(What:=sStr, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlwhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
if len(rng) > 2 then
if mid(rng,3,1) <> " " then
If rng2 Is Nothing Then
Set rng2 = rng
Else
Set rng2 = Application.Union(rng2, rng)
End If
end if
end if
Set rng = .FindNext(rng)
Loop while rng.Address <> FirstAddress
End If
End With
If Not rng2 Is Nothing Then rng2.Select
End Sub

 

[okrob]

str is a function. don't use it as a variable name. Note I also change
xlPart to xlWhole

Sub Union_in_column()
Dim FirstAddress As String
Dim sStr As String
Dim rng As Range
Dim rng2 As Range
sStr = " *" ' two spaces and an asterick
With Range("C:C")
Set rng = .Find(What:=sStr, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlwhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
if len(rng) > 2 then
if mid(rng,3,1) <> " " then
If rng2 Is Nothing Then
Set rng2 = rng
Else
Set rng2 = Application.Union(rng2, rng)
End If
end if
end if
Set rng = .FindNext(rng)
Loop while rng.Address <> FirstAddress
End If
End With
If Not rng2 Is Nothing Then rng2.Select
End Sub

--
Regards,
Tom Ogilvy








- Show quoted text -

thx... works great.