Searching for specific case
 

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

Searching for specific case
 

Nevermind...

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



On Feb 2, 10:45 am, "okrob" wrote:
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

Searching for specific case
 

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


"okrob" wrote:

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

Searching for specific case
 

On Feb 2, 11:09 am, Tom Ogilvy
wrote:
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



"okrob" wrote:
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- Hide quoted text -

- Show quoted text -

thx... works great.