worksheet code

[nowfal]

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

 

[Dave Peterson]

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