Extract Text

[sswcharlie]

Trying to get the following to work, Keeps coming up with error in 'Find". I have tried various changes but with no luck. Keeps coming back to 'Find'

A simple error I think ?


Charlie


*******
Sub Extract_Text_Click()


Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long

Data_rows = Range(A1, A2000.End(xlDown)).Rows.Count


For i = 1 To Data_rows

Range("K" & i).Value = Right(Range("A" & i), Find("name,", _
Range("A" & i), 2) + 20).Value

Next i

End Sub

[JimmyTheHand]

Hello Charlie, welcome to VBAX

The one you showed is an incorrect sytax of using Find method.
Find is used to find cells with a certain value. The below example looks for value "123" in column A
[vba]Dim c As Range
Set c = Activesheet.Range("A:A").Find(What:="123")
[/vba] Though you did not specify what you wanted to accomplish, (which is, by the way, advised, if you want to get helpful answers,) I think you need the InStr function rather than Find method.

Jimmy

[sswcharlie]

Hi

I want to extract the text required that follows - "name"
Text (city names)can vary in length

eg name New York

New York is the name I wish to extract and offset to a column, say column K to the right, and in the next available blank cell in the column.

Thks


Charlie

[p45cal]

To put the results in the same row in column K, maybe you're looking for:

Data_rows = Range("A1", Range("A2000").End(xlDown)).Rows.Count
For i = 1 To Data_rows
Range("K" & i).Value = Application.Trim(Mid(Range("A" & i), InStr(Range("A" & i), "name,") + 5, 20))
Next i

or replace the line in the middle of the loop with one of these:

Range("K" & i).Value = Application.Trim(Mid(Range("A" & i), Application.Find("name,", Range("A" & i)) + 5, 20))

Range("K" & i).Value = Mid(Range("A" & i), Application.Find("name,", Range("A" & i)) + 5, 20)

Range("K" & i).Value = Mid(Range("A" & i), InStr(Range("A" & i), "name,") + 5, 20)

The first line above is in danger of choosing an awful lot of lines to process.
The lines with 'Application.Trim' in, use the worksheet version of Trim to strip off extra spaces both at each end and within the string.

p45cal

[sswcharlie]

Thanks. I will try all versions out.

Charlie

[JimmyTheHand]

Try this one, too.
[vba] Sub Extract_Text()
Dim i As Long, Data_rows As Long
Dim Pos As Long, N As String

Data_rows = Range("A1", Range("A2000").End(xlDown)).Rows.Count
For i = 1 To Data_rows
Pos = InStr(Range("A" & i), "name,")
If Pos > 0 Then
N = Mid(Range("A" & i), Pos + 5)
If Range("K:K").Find(What:=N, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
Range("K" & Rows.Count).End(xlUp).Offset(1) = N
End If
End If
Next
End Sub[/vba]
This part:
[vba] If Range("K:K").Find(What:=N, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
[...]
End If
[/vba] ensures that there are no duplicate names in column K.
If you want names listed as many times as they occur, delete the two lines above (but leave the one line between alone).

Pay attention to the character separating keyword "name" and the actual name. Your example was "name New York", with space in between, while your code showed "name,". That's a comma separator. The code will not work if the two are confused.

Also, I agree with p45cal that the range definition of
Range("A1", Range("A2000").End(xlDown))
looks not very good. It's not flexible, and, in case there are no filled rows below row 2000, can force the code to process ca. 63000 empty lines, which is a total waste of time and resources. You should think this definition over.

Jimmy

[Bob Phillips]

<snip>
p45cal

Is p45cal Pascal Daulton?

[sswcharlie]

Thanks for the codes. I have now added a second set of commands selecting using a slightly different reference.

In column A I have one set of information of several lines. This works fine and I get the results required. That is the first set of code picks a time and then the second set of codes picks other times. Note that with the first part of code there will always only be one time to select, and must come first and with the second part of code there will be one or more times to select.

Now I need to run a loop to go down column A with more than one set of data so that it keeps obtaining info in the right order as above.

Data looks like this:

*******

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

24467

23445

GRAINCAR

GRAINCAR

Grn w/Wht Lttrs -4 Bay

Maroon w/Wht Ltr -3 Bay

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

***************

Each new set of data starts with "SWITCHLIST"

Which is the best way to go about this. Use For...Next Loop ?
Loop will stop when it reaches the first blank cell in column.
There are no blank rows until end.


Code is:


Dim i As Long, Data_rows As Long
Dim Pos As Long, N As String

Data_rows = Range("A1", Range("A2000").End(xlDown)).Rows.Count
For i = 1 To Data_rows
Pos = InStr(Range("A" & i), "WN is")
If Pos > 0 Then
N = Mid(Range("A" & i), Pos + 5)
If Range("K:K").Find(What:=N, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
Range("K" & Rows.Count).End(xlUp).Offset(1) = N
End If
End If
Next


Data_rows = Range("A1", Range("A2000").End(xlDown)).Rows.Count
For i = 1 To Data_rows
Pos = InStr(Range("A" & i), "ng at")
If Pos > 0 Then
N = Mid(Range("A" & i), Pos + 5)
If Range("K:K").Find(What:=N, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
Range("K" & Rows.Count).End(xlUp).Offset(1) = N
End If
End If
Next

End Sub
*********

Thks Charlie

[sswcharlie]

Furhter to previous message.

Just realised that I should combine the two codes into one as say a
and/or statement or similiar.

Thoughts?
Charlie