Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for specific text - how to | Excel Worksheet Functions | |||
Searching for case specific data | Excel Discussion (Misc queries) | |||
do formulas have to be case sensative when searching words | Excel Discussion (Misc queries) | |||
searching for specific text | Excel Discussion (Misc queries) | |||
Searching for specific text | Excel Programming |