A
AD108
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
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