nepotist
03-24-2009, 12:47 PM
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
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