Worksheet Change Event

[sachavez]

Excel 2010

Need some VBA assistance with a worksheet change event.

I need the code to reformat the contents in range G12:G29.

I'm pasting the data in from a main frame screen, which produces a date format of 620 (June 20, 2012). So, I need the code to reformat 620 to 6/20, etc.

Thanks, in advance!

Steve

 

[Jonmo1]

Question..

Take the number 111
Is that January 11, or November 1 ??

You'll need some method to differentiate that.

 

[sachavez]

the mainframe produces 1111 for November 11th and 0111 for January 11th. Does that help?

 

[Jonmo1]

That contradicts your original post, where June (single digit month) 20th is 620, not 0620

Is it actually consistantly a 4 digit value, DDMM

January 1st would be 0101

 

[sachavez]

The mainframe format is MMDD. Today's data is showing as 0620 (I originally left out the zero in my original post; when pasted into Excel, the leading zero doesn't show).

I hope this helps.

Thanks!

 

[Jonmo1]

In any case, this will work if the data is a consistent 4 digit value MMDD

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, MyRange As Range
Set MyRange = Intersect(Target, Range("G12:G29"))
If MyRange Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In MyRange
    If Len(c) > 0 Then
        c = Left(c, 2) & "/" & Right(c, 2)
    End If
Next c
Application.EnableEvents = True
End Sub

 

[sachavez]

Thank you for the code.

The format is off a bit; it is converting 0620 to 62/20.

Steve

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Jonmo1]

Then the value in the cell is NOT actually 0620, it's 620 Formatted as 0620

Try changing
c = Left(c, 2) & "/" & Right(c, 2)
to
c = Left(c.Text, 2) & "/" & Right(c.Text, 2)

 

[sachavez]

Ok, that worked. Now, when I past the data in a second time, I the result is 1/ep. this is very strange. Does the code need to format the range as a date?

 

[Jonmo1]

Do you want it to actually be a real date recognized by Excel?