Worksheet Change Event

sachavez

Active Member
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.

Steve

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Question..

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

You'll need some method to differentiate that.

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

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

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!

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

Code:
``````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``````

Thank you for the code.

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

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

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)

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?

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

Replies
3
Views
741
Replies
6
Views
725
Replies
1
Views
437
Replies
1
Views
189
Replies
4
Views
394

1,203,257
Messages
6,054,408
Members
444,723
Latest member
Gagan sree ram

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back