Hi,
I wanted to add one more cell to this sheet change code, its alread
a cell(A10)
the second cell is (B4). I have tried lot, not getting the result.
hope somebody will be help.


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$10" Then
If Len(.Value) 62 Then
iPos = InStrRev(.Value, " ", 63)
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


with regards
nowfal

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39939

I guess that you mean

If .Address = "$A$10" Or .Address = "$B$4" Then


--

HTH

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


"nowfal" wrote in
message ...

Hi,
I wanted to add one more cell to this sheet change code, its already
a cell(A10)
the second cell is (B4). I have tried lot, not getting the result. I
hope somebody will be help.


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$10" Then
If Len(.Value) 62 Then
iPos = InStrRev(.Value, " ", 63)
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


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=399399

Hi Bob,
Yes both . first to write for cells A10 then for B4. Hope
you got the point. And expecting your valuable suggestion.
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=399399

The line I gave you will trap changes to either A10 or B4 and split the
value in the cell that has been changed.

Is there something else that you want to do with it?

--

HTH

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


"nowfal" wrote in
message ...

Hi Bob,
Yes both . first to write for cells A10 then for B4. Hope
you got the point. And expecting your valuable suggestion.
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=399399