I'm using the following code to reformat dates in 3 different columns,
for example, Column T goes from 2070126 to 01/26/2007. What I'd like
to do is add something to it that will check each column first to make
sure it hasnt already been reformatted. Any ideas?


Sub CleanUp_Data_File()

Application.ScreenUpdating = False

With ActiveSheet

' ~~ Reformat Column P ~~
Columns("P:P").Insert
Range("P3:P" & Range("Q65536").End(xlUp).Row).FormulaR1C1 = _
"=MID(RC[1],5,2)&""-""&MID(RC[1],1,3)&""-""&MID(RC[1],8,4)"
Range("P3:P" & Range("P65536").End(xlUp).Row).Copy
Range("Q3").PasteSpecial xlPasteValues, , False, False
Columns("P:P").EntireColumn.Delete

' ~~ Reformat Column S ~~
Columns("S:S").Insert
Range("S3:S" & Range("T65536").End(xlUp).Row).FormulaR1C1 = _
"=MID(RC[1],4,2)&""/""&MID(RC[1],6,2)&""/""&""20""&MID(RC[1],
2,2)"
Range("S3:S" & Range("S65536").End(xlUp).Row).Copy
Range("T3").PasteSpecial xlPasteValues, , False, False
Columns("S:S").EntireColumn.Delete

' ~~ Reformat Column T ~~
Columns("T:T").Insert
Range("T3:T" & Range("U65536").End(xlUp).Row).FormulaR1C1 = _
"=MID(RC[1],4,2)&""/""&MID(RC[1],6,2)&""/""&""20""&MID(RC[1],
2,2)"
Range("T3:T" & Range("T65536").End(xlUp).Row).Copy
Range("U3").PasteSpecial xlPasteValues, , False, False
Columns("T:T").EntireColumn.Delete

End With

Application.ScreenUpdating = True

End Sub


Thanks,
-- Dan

Sub macro1()
Dim iEnd As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range
Dim iCt As Integer

Set ws = ActiveSheet
cols = Array("P", "S", "T")
For iCt = 0 To 2
iEnd = ws.Cells(65536, cols(iCt)).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, cols(iCt)), Cells(iEnd,
cols(iCt)))
For Each c In rng
If InStr(c, "/") = False Then
c = Mid(c, 4, 2) & "/" & Mid(c, 6, 2) & "/" & "20" &
Mid(c, 2, 2)
Next c
Next iCt
End Sub

Hth,
Merjet

Wow, I didn't know you could use Mid like that... It gave me a 'Next
without For' error and highlighted "Next c" on the 3rd row from the
bottom. Also, I hate to pry but the formatting for Column P goes from
Jan 26 2007 12:00AM to 26-Jan-2007... Do you mind showing me how to
write that more efficiently as well?

Thank You,
-- Dan

I suspect the errors were because lines in my code were broken when
posted. 'cols(iCt)))' goes with the line above it. Same for 'Mid(c, 2,
2)'.

I hate to pry but the formatting for Column P
goes from Jan 26 2007 12:00AM to 26-Jan-2007...

Not clear enough. Is that before or after reformating? Please post a
complete example of a 'before' and an 'after'.

Merjet

P.S. " c = Mid(c, 4, 2) ... " goes with Then, or put " _" after
Then.

Merjet

Yep, that was it...

For column P, here's the formula I'm using in the spreadsheet:
=MID(P3,5,2)&"-"&MID(P3,1,3)&"-"&MID(P3,8,4)

Befo
Jan 26 2007 12:00AM
Jan 3 2007 12:00AM
Jan 3 2007 12:00AM
Jan 26 2007 12:00AM

After: (time is excluded)
26-Jan-2007
3-Jan-2007
3-Jan-2007
26-Jan-2007

Thanks Merjet

I can't think of anything more efficient than what you have.

Merjet