First Macro Issues: Range selection

Hi Everyone. I'm trying to write my first macro to convert date formats. I have some data that is in an odd date format such as 1980326. In my example the date is 03/26/1998. The first digit, either 1 or 2, needs to be replaced with 19 or 20 and then I want it to be combined and reformatted to mm/dd/yyyy.

Here's what I have so far. I have it insert 7 columns after the current date format (in case there is other data in the file that we don't want to overwrite). It then splits the data, changes the first number, concatenates it, and drags the formula. My problem is coming when I try to copy the data. I'm not sure why but I'm receiving an error with the Range(Selection, Selection.End(x1Down)).Select command. Does anyone know why this is happening? Thanks!

Please Login or Register  to view this content.

A larger problem is how can you know what 2013121 means. Is it 1/21/2013, or 12/1/2013? You're going to have a problem with January, November, and December dates.

No...I'm not. The format is always the same. Your date is invalid because it is 31/21/2001.

If day and month are always represented as single digit without delimiters in the source data, no computer can know whether "121" means "1/21" or "12/1". If the data represents "12/1/2013" as "20131201", you can program for that, but there's no way around the fact that "2013121" is an ambiguous date.

I currently have it so it splits the data up from 1980326 into 6 different columns. It then takes the first column (the 1 or 2) and uses a find/replace function an all of the numbers to change them to 19 or 20. I then used concatenate to combine the 6 columns with the forward slashes in them.

Copy my code into a macro in excel. Make cell A1 and B1 have these weird dates. Use 1980326 and 2010512. Run this part of my code and it will work for any number of these weird dates.

Please Login or Register  to view this content.

My problem is coming when I'm trying to select a column of numbers. Like this one here for example.

1181006
1210713
1550319
1581015
1500315
1480208
1520708
1530423
1530411
1530325
1520715
1370911
1471126
1510602
1530105
1450505
1440205
1470504
1391025
1560102
1571009
1450830
1530422
1540724
1490206
1560824
1531016
1601008
1540604
1510430
1451105
1620331
1390505
1400113
1460727
1521002
1610214
1570131
1540217
1541118
1460203
1500216
1550930
1640512
1570309
1431212
1560620
1510317
1530628
1530829
1330609
1540617
1660107
1560716
1560409
1530203
1470211
1570619
1551119
1421107
1520322
1480401
1590904
1460710
1510320
1360323
1360512
1491110
1490224
1390311
1600703
1490829
1430325
1421016
1561228
1550616
1460605
1610829
1420425
1430527
1440918
1400803
1480223
1630514
1561208
1620214
1640201
1420731
1560511
1650729
1601230
1631224
1510420
1530817
1461030
1491124
1521118
1360211
1521212
1430119
1371003
1370318
1551003
1450129
1390121
1380507
1450509
1380913
1550627
1480820
1581230
1640729
1470404
1290928
1580108
1600406
1510224
1570117
1510321
1610922
1500507
1591215
1580426
1510322
1560316
1620919
1480506
1530506
1500805
1390216
1421120
1390526
1490113
1560721
1490225
1440715
1441002
1601030
1630830
1550527
1510912
1280504
1520204
1400730
1470403
1470527
1310922
1300529
1230310
1301122
1270823
1260602
1280228
1410531
1200414
1290106
1220606
1260415

Ah nevermind I misunderstood the odd date format, month and day are represented by 2 digits each so you're fine.

The problem is the "x1down", it should be "xldown", after that change the code works for me.

Ohhh it's an L? I thought it was a number . Well thanks for making me look stupid. I'll look into your more elegant code once I am back on the machine with the data. Thank you!