I have sorted a couple of minor syntax problems in the VBA code, but
now when I press the comand button nothing results. Not even a
message to say there is a problem or any text extracted into column
k.
Syntax problems were a ' required and a _ required.
I can folow the code as to what it does up until near the end and I
havent worked out what the 2)+20) does ? Can you give me an
insight into that.
Can you give me a guidence to sort.
I have listed the data (3 sets) to be used and the code I am using
below.
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS
Terminal Shipping Burl North 460020
Terminal Shipping Sante Fe 100396
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS
GRAINCAR
GRAINCAR
empty
empty
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Burl North
Sante Fe
460020
100396
GRAINCAR
GRAINCAR
Grn w/Wht Lttrs -4 Bay
Maroon w/Wht Ltr -3 Bay
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -East
DEPARTURE TIME from WESTTOWN is 01:10
TOWN STOP---BUSNES TWN Arriving at 01:40
PICKUPS
Wholsale Co. Ltd. Candn Natl 78358 BOXCAR load Downtown Mall
Train should leave this town with 1 car(s)
TOWN STOP---MFG TWN Arriving at 02:10
PICKUPS
Furniture Factory Grand Trunk 3780
Train should leave this town with 2 car(s)
TOWN STOP---SHOPNG TWN Arriving at 03:10
SETOUTS
LONGBOX load Downtown Mall
Downtown Mall
Downtown Mall
Grand Trunk
Candn Natl
3780
78358
LONGBOX
BOXCAR
82' Blu w/Wht Lttr
40' Org w/Gld Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Easttwn Wayfrght
DEPARTURE TIME from EASTTOWN is 01:20
TOWN STOP---SHOPNG TWN Arriving at 01:50
PICKUPS
Downtown Mall CN RailBox 098 BOXCAR empty Wholsale Co. Ltd.
Downtown Mall Sante Fe 20634 BOXCAR empty Wholsale Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---BUSNES TWN Arriving at 02:50
SETOUTS
Wholsale Co. Ltd. CN RailBox 098 BOXCAR 40' Ylw w/Blk Lttr
Wholsale Co. Ltd. Sante Fe 20634 BOXCAR 40' Brn w/Wht Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -West
DEPARTURE TIME from EASTTOWN is 01:25
PICKUPS
East Freight Yard Candn Govt 106200 GRAINCAR load Terminal Shipping
Train should leave this town with 1 car(s)
TOWN STOP---SEAPRT TWN Arriving at 03:55
SETOUTS
Terminal Shipping Candn Govt 106200 GRAINCAR Ylw & Sil w/Red Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS
Terminal Shipping Burl North 460020
Terminal Shipping Sante Fe 100396
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS
GRAINCAR
GRAINCAR
empty
empty
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Burl North
Sante Fe
460020
100396
GRAINCAR
GRAINCAR
Grn w/Wht Lttrs -4 Bay
Maroon w/Wht Ltr -3 Bay
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -East
DEPARTURE TIME from WESTTOWN is 01:10
TOWN STOP---BUSNES TWN Arriving at 01:40
PICKUPS
Wholsale Co. Ltd. Candn Natl 78358 BOXCAR load Downtown Mall
Train should leave this town with 1 car(s)
TOWN STOP---MFG TWN Arriving at 02:10
PICKUPS
Furniture Factory Grand Trunk 3780
Train should leave this town with 2 car(s)
TOWN STOP---SHOPNG TWN Arriving at 03:10
SETOUTS
LONGBOX load Downtown Mall
Downtown Mall
Downtown Mall
Grand Trunk
Candn Natl
3780
78358
LONGBOX
BOXCAR
82' Blu w/Wht Lttr
40' Org w/Gld Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Easttwn Wayfrght
DEPARTURE TIME from EASTTOWN is 01:20
TOWN STOP---SHOPNG TWN Arriving at 01:50
PICKUPS
Downtown Mall CN RailBox 098 BOXCAR empty Wholsale Co. Ltd.
Downtown Mall Sante Fe 20634 BOXCAR empty Wholsale Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---BUSNES TWN Arriving at 02:50
SETOUTS
Wholsale Co. Ltd. CN RailBox 098 BOXCAR 40' Ylw w/Blk Lttr
Wholsale Co. Ltd. Sante Fe 20634 BOXCAR 40' Brn w/Wht Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -West
DEPARTURE TIME from EASTTOWN is 01:25
PICKUPS
East Freight Yard Candn Govt 106200 GRAINCAR load Terminal Shipping
Train should leave this town with 1 car(s)
TOWN STOP---SEAPRT TWN Arriving at 03:55
SETOUTS
Terminal Shipping Candn Govt 106200 GRAINCAR Ylw & Sil w/Red Lttr
CODE
Option Explicit
Sub Extract_Text_Click()
' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
' Determine number of rows in user selected column to loop through.
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count
' Start looping through column A
For i = 1 To Data_rows
' extract the text, and paste into column K using offset
' function - Below not tested. Should be easy to make it work,
though.
Range("K" & i).Value = Right(Range("A" & i), Find("STOP---", _
Range("A" & i), 2) + 20).Value
Next i
End Sub