Check values before running macro

[Dan R.]

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").Insert
Range("P3" & Range("Q65536").End(xlUp).Row).FormulaR1C1 = _
"=MID(RC[1],5,2)&""-""&MID(RC[1],1,3)&""-""&MID(RC[1],8,4)"
Range("P3" & Range("P65536").End(xlUp).Row).Copy
Range("Q3").PasteSpecial xlPasteValues, , False, False
Columns("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

 

[merjet]

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

 

[Dan R.]

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

 

[merjet]

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

 

[merjet]

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

Merjet

 

[merjet]

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

Merjet

 

[Dan R.]

Sorry I'm not the best at explaining things... in the code you posted
above all 3 columns are reformatted as 2070126 to 01/26/2007, whereas
only S and T should be formatted like that, Column P needs to be
reformatted as 12:00AM to 26-Jan-2007. I've tried make the necessary
changes to your original code but I can't seem to get it to work....
Here's what I've come up with:


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

Application.ScreenUpdating = False

Set ws = ActiveSheet
cols = Array("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

cols = Array("P")
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, 5, 2) & "-" & Mid(c, 1, 3) & "-" & Mid(c,
8, 4)
Next c
Next iCt

Application.ScreenUpdating = True

End Sub


Thanks,
-- Dan

 

[Dan R.]

oops... that is column P should be reformatted from Jan 26 2007
12:00AM to 26-Jan-2007

 

[Dan R.]

Fixed it... Thanks again for all your help Merjet!
-- Dan

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

Application.ScreenUpdating = False

Set ws = ActiveSheet
iEnd = ws.Cells(65536, 19).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, 19), Cells(iEnd, 19))
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

iEnd = ws.Cells(65536, 20).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, 20), Cells(iEnd, 20))
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

iEnd = ws.Cells(65536, 16).End(xlUp).Row
Set rng = ws.Range(ws.Cells(3, 16), Cells(iEnd, 16))
For Each c In rng
If InStr(c, "-") = False Then _
c = Mid(c, 5, 2) & "-" & Mid(c, 1, 3) _
& "-" & Mid(c, 8, 4)
Next c

Application.ScreenUpdating = True

End Sub