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

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If Len(.Value) 75 Then
iPos = InStrRev(.Value, " ", 76)
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



--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" wrote in
message ...

Hi Mr.Bob
The latest code is working, but 76th character is going
to B2 that is the next cell. For me i need it to go to the next line,
that is A2. so pls try to sort it out.
with regards
nowfal


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