Now, seeing the dataset you want to process, I would approach it very
differently:
1) As before, determine the number of rows to process
2) For each line in the rows, if starts with "TOWN STOP---", then
copy to column k
Replace "TOWN STOP---" with ""
Else
End If
Now, since I do not know the correct format for the Replace function, I
am going to record a macro to do that.
ActiveCell.Replace What:="TOWN STOP---", Replacement:="", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Activecell is not appropriate. We will replace that with a Range()
statement later on.
------------------------------------------------------------------------
-
Using Google Group Search, I searched for "text contains" and found that
I could use "InStr" as a command to test whether "Town Stop---" is in
the string or not.
Code now looks as follows:
Sub Extract_Text_Click()
' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
Dim Searchstring, Searchchar, MyPos
' Determine number of rows in user selected column to loop through.
' First, select column A
Range("A1").Select
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count
' Start looping through column A
For i = 1 To Data_rows
Searchchar = "TOWN STOP---"
Range("A1").Offset(i, 0).Select
Searchstring = ActiveCell.Value
MyPos = InStr(1, Searchstring, Searchchar, 1)
Debug.Print Searchstring, "|||", Searchchar, "|||", MyPos
Next i
End Sub
-----------------------------------
The debug.print shows me in the Immediate Window (In VB Editor, select
from View menu if not visible) that MyPos is zero, UNLESS "Town Stop---"
is present.
Cool. Now we can say: If MyPos = 1 then
' copy to column k
' Replace "TOWN STOP---" with ""
Else
'Do nothing
End If
-------------------------------------------------------------- Which
gives the final code of------------------------------
Option Explicit
Sub Extract_Text_Click()
' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
Dim Searchstring, Searchchar, MyPos
' Determine number of rows in user selected column to loop through.
' First, select column A
Range("A1").Select
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count
' Start looping through column A
For i = 1 To Data_rows
Searchchar = "TOWN STOP---"
Range("A1").Offset(i, 0).Select
Searchstring = ActiveCell.Value
MyPos = InStr(1, Searchstring, Searchchar, 1)
Debug.Print Searchstring, "|||", Searchchar, "|||", MyPos
If MyPos = 1 Then Range("k" & i).Value = Searchstring
' We now have the value in column K, now we need to remove the
Town Stop bit.
Range("k" & i).Replace What:="TOWN STOP---", Replacement:="",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
'Lastly, If you just want the town name, I see that the string "Arriving
at" is also
'present in all instances. Using "Left" or RIGHT" and another InStr to
dedermine where
' "Arriving at" starts, you can delete that programmatically as well.
'
' Code executes very quickly. If you want to delete/hide all rows not
containing values
' in column K, see one of the resources I mentioned earlier.