Hi,
Below i have pasted 2 codes, one is worksheet another standard i
wanted to join these two codes and to become a standard code. Any idea
?
thanks and regards
nowfal

-----------------------------------------------------------------------------

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) 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

-------------------------------------------------------------------------------
GBPREINST Macro
' Macro recorded 18/08/2005 by Musandam
'
Range("B8,B9").ClearContents
Range("B8").Select
Windows("MICR.xls").Activate
Range("N2").Select
Windows("MICR PRINT.xls").Activate
ActiveCell.FormulaR1C1 = "=MICR.xls!R2C14"
Range("B4").Select
Sheets("DA").Select
Range("A9,a10,a11").ClearContents
Range("A9").Select
Windows("MICR PRINT.xls").Activate
ActiveCell.FormulaR1C1 = "=MICR.xls!R2C15"
Range("a10").Select
ActiveCell.FormulaR1C1 = "=SPELLNUMBER(R[-1]C,""GBP"")"
Range("A2").Select
Sheets("GBP").Select
Range("b4").Select
End Sub


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

I may be over-simplifying it, but this triggered by a change in B7 splits
the results from SPELLNUMBER into 2 cells

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$7" Then
With .Offset(0, 1)
.Value = SPELLNUMBER(.Value, "GBP")
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 With
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,
Below i have pasted 2 codes, one is worksheet another standard i
wanted to join these two codes and to become a standard code. Any idea
?
thanks and regards
nowfal

--------------------------------------------------------------------------
---

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) 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

--------------------------------------------------------------------------
-----
GBPREINST Macro
' Macro recorded 18/08/2005 by Musandam
'
Range("B8,B9").ClearContents
Range("B8").Select
Windows("MICR.xls").Activate
Range("N2").Select
Windows("MICR PRINT.xls").Activate
ActiveCell.FormulaR1C1 = "=MICR.xls!R2C14"
Range("B4").Select
Sheets("DA").Select
Range("A9,a10,a11").ClearContents
Range("A9").Select
Windows("MICR PRINT.xls").Activate
ActiveCell.FormulaR1C1 = "=MICR.xls!R2C15"
Range("a10").Select
ActiveCell.FormulaR1C1 = "=SPELLNUMBER(R[-1]C,""GBP"")"
Range("A2").Select
Sheets("GBP").Select
Range("b4").Select
End Sub


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