One way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Intersect(.Cells, Me.Range("b8,a10")) Is Nothing Then
Exit Sub
End If
If .Cells.Count 1 Then
Exit Sub
End If
If Len(.Value) 50 Then
iPos = InStrRev(.Value, " ", 51)
If iPos 0 Then
.Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
.Value = Left(.Value, iPos)
End If
End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

nowfal wrote:

Hi,
Is it possible to add one more cell in the below work sheet code
("$A$10").

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$B$8" Then
If Len(.Value) 50 Then
iPos = InStrRev(.Value, " ", 51)
If iPos 0 Then
Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
Value = Left(.Value, iPos)
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

thanks and regards
nowfal

--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=397325

--

Dave Peterson