Help with the error

[nepotist]

If have the following code to split the text from one cell to three different cells
Sub txt2column()
'On Error GoTo line1:

Dim txtSegment As String
Dim P As Integer
Dim u As Integer
Dim lent As Integer
Dim rw As Integer
Dim txtFrom As String
Dim txtTo As String


With ActiveSheet
rw = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
On Error GoTo line1:
For rw = rw To 3 Step -1


P = Application.WorksheetFunction.Find(" FROM ", Cells(rw, "c"))
u = Application.WorksheetFunction.Find(" TO ", Cells(rw, "c"))
lent = Len(Cells(rw, "c").Value)

txtSegment = Left(Cells(rw, "c").Value, P - 1)

txtTo = Right(Cells(rw, "c").Value, lent - u - 3)
txtFrom = Mid(Cells(rw, "C").Value, P + 5, u - P - 5)
Cells(rw, "d").Value = txtSegment
Cells(rw, "f").Value = txtTo
Cells(rw, "E").Value = txtFrom

line1:
If Err.Number = 1004 Then
Cells(rw, "d").Value = Cells(rw, "C").Value
End If

Next rw

End Sub



well the logic and the code works fine as long as the reference cell has From and To words in it but when there is no From and To I wasnt to copy paste the cell in to the first destination cell.

The problem I am having with the above code is that the On error go to line works only once and the next time it gives me the error mesage back again.

I am looping the on error statement right?

Can some one please help me fix it thanks

[CreganTur]

Try adding an err.Clear line somewhere after your Line1 label.

[nepotist]

well first question is since when did you start helping people in Excel too??
I used to see you in Access all the time.
Good to see you here.

well that dosent help me it works fine when it comes across the error first time but second time it pops it back up . :(

[mdmackillop]

Can you post some data to demonstrate your code and showing your desired results?

[nepotist]

here is a example spread sheet

[nepotist]

Anyone?? any ideas
:dunno:help:shouting:

[mdmackillop]

I can't open your file.

[nepotist]

Y is there any thing wrong?There awas initially a ddin to it.. I have removed the add in .. try to see if you can this time.

Thanks for your time

[Aussiebear]

Can't open it either MD. Error msg suggests it is not of the file type extension that it suggests that it is.

[mdmackillop]

Sub txt2column2()
Dim rw As Long
rw = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).Row
For rw = rw To 3 Step -1
txt = Cells(rw, "C")
If InStr(1, txt, " from ") = 0 And InStr(1, txt, " to ") = 0 Then
Cells(rw, "D") = txt
Else
Cells(rw, "D") = Split(txt, " from ")(0)
Cells(rw, "F") = Split(txt, " to ")(1)
txt = Split(txt, " from ")(1)
Cells(rw, "E") = Split(txt, " to ")(0)
End If
Next rw
End Sub

[nepotist]

Thank you that works like a charm.. :D
:bow::bow::yay