text manipulation in change event
 

I am using the following code in the worksheet change event module.
My goal is to have a date entered in cell G1 transformed into the following
format.

013106-MAUI

A custom number format may not work, as sometimes the ending text is
different. In the code below I have the ending text coming from cell D1.

In my code, the user enters the date which is effected by the custom format
"mmddyy".
A formula (=text(G1,"MMDDYY") in another cell ("CH1") converts this to text
for use by the code.

My code works except that it is dropping the leading zero. Any ideas how to
stop it from doing that.

Thanks



Public Sub ChangeData(ByVal target As Range)
Dim objConn As ADODB.Connection
Dim rsData, rsPO, rsPrice As ADODB.Recordset
Dim sConnStr, sCmtText As String

'Ariel's - Vincent, ignore this
Dim strStore As String
Dim strFormat As String
Application.EnableEvents = False

If target.Address = Range("G1").Address Then
Range("G2").FormulaR1C1 = _

"=R1C7+(INDEX(R4C77:R25C81,MATCH(R[7]C4,R4C77:R25C77,0),MATCH(R1C4,R4C77:R4C
81,0)))"
Range("G3").FormulaR1C1 = _

"=R1C7+(INDEX(R4C77:R25C81,MATCH(R[6]C4,R4C77:R25C77,0),MATCH(R1C4,R4C77:R4C
81,0)+1))"
Range("G2:G3").Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'strFormat = "mmddyy"
Range("G1").NumberFormat = "Text"
Range("G1").Value = str(Range("CH1").Value)
Debug.Print str(Range("CH1").Value)
strStore = str(Range("G1").Value) & "-" & Range("D1").Value
Range("G1").Value = strStore

Debug.Print strStore
End If
Application.EnableEvents = True

text manipulation in change event
 

Try this one:

strStore = Chr(39) & Str(Range("G1").Value) & "-" & Range("D1").Value





"AD108" schreef in bericht
...
I am using the following code in the worksheet change event module.
My goal is to have a date entered in cell G1 transformed into the
following
format.

013106-MAUI

A custom number format may not work, as sometimes the ending text is
different. In the code below I have the ending text coming from cell D1.

In my code, the user enters the date which is effected by the custom
format
"mmddyy".
A formula (=text(G1,"MMDDYY") in another cell ("CH1") converts this to
text
for use by the code.

My code works except that it is dropping the leading zero. Any ideas how
to
stop it from doing that.

Thanks



Public Sub ChangeData(ByVal target As Range)
Dim objConn As ADODB.Connection
Dim rsData, rsPO, rsPrice As ADODB.Recordset
Dim sConnStr, sCmtText As String

'Ariel's - Vincent, ignore this
Dim strStore As String
Dim strFormat As String
Application.EnableEvents = False

If target.Address = Range("G1").Address Then
Range("G2").FormulaR1C1 = _

"=R1C7+(INDEX(R4C77:R25C81,MATCH(R[7]C4,R4C77:R25C77,0),MATCH(R1C4,R4C77:R4C
81,0)))"
Range("G3").FormulaR1C1 = _

"=R1C7+(INDEX(R4C77:R25C81,MATCH(R[6]C4,R4C77:R25C77,0),MATCH(R1C4,R4C77:R4C
81,0)+1))"
Range("G2:G3").Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'strFormat = "mmddyy"
Range("G1").NumberFormat = "Text"
Range("G1").Value = str(Range("CH1").Value)
Debug.Print str(Range("CH1").Value)
strStore = str(Range("G1").Value) & "-" & Range("D1").Value
Range("G1").Value = strStore

Debug.Print strStore
End If
Application.EnableEvents = True

text manipulation in change event
 

in this example u have 13106 in A1 and -MAUI in B1

Cells(1, 3) = Format(Cells(1, 1).Text, "000000") & Cells(1, 2)

result in B3 = 013106-MOUI

text manipulation in change event
 

Thanks very much.
"excelent" wrote in message
...
in this example u have 13106 in A1 and -MAUI in B1

Cells(1, 3) = Format(Cells(1, 1).Text, "000000") & Cells(1, 2)

result in B3 = 013106-MOUI