Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Text During Change Event | Excel Programming | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation | Excel Programming | |||
Fine-tuning selection change event for merged cells & wrap text | Excel Programming | |||
Text manipulation | Excel Programming |