Donnie
The range in an adjacent column would be one which would extend from the top
of column B to the bottom of your data in column A. I have used A2 and B2
so's you could have a title row if needed.
This re-written macro will select cells in column B down to the end of data in
column A and insert the formulas.
NOTE: lines 3 and 4 are one long line.
After running the macro you can select column B and CopyPaste SpecialValues
then delete Column A. Use the macro recorder to record these steps and plug
them into Sub change_dates_2() after the Columns(2).Autofit line.
Sub change_dates_2()
Dim a As Long
Range("B2").Formula = "=DATE(VALUE(""20"" & MID(A2,2,2)),
VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))"
a = Range("A" & Rows.Count).End(xlUp).Row
Range("B2:B" & a).FillDown
Columns(2).Autofit
End Sub
If new to macros you may want to visit David McRitchie's site first.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Gord
On Sun, 2 Nov 2003 21:11:55 -0500, "Donnie Stone"
wrote:
Gord,
I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?
Regards,
Donnie
<Gord Dibben wrote in message
.. .
Donnie
Manually enter this in an adjacent column and copy down by double-clicking
on
the right-hand bottom corner of the cell.
=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))
Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.
If need a macro......
Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub
Watch for wordwrap.
Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.
Gord Dibben XL2002
On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:
I routinely import files from an AS400 to excel and the dates appear as
follows:
A
1 1031102
2 1031103
A1 equals 11/02/03 & A2 equals 11/03/03.
I'm looking for some help with a macro that would convert the dates
automatically.
Thanks,
Donnie