Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
P.S. " c = Mid(c, 4, 2) ... " goes with Then, or put " _" after
Then. Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
I can't think of anything more efficient than what you have.
Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
oops... that is column P should be reformatted from Jan 26 2007
12:00AM to 26-Jan-2007 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check values before running macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing Check Boxes & Running a diff Macro when unchecking the ch | Excel Discussion (Misc queries) | |||
How to check a cell for content before running macro. | Excel Programming | |||
How to Check if a Process is Running | Excel Programming | |||
check for running app | Excel Programming | |||
Check for Excel version before running? | Excel Programming |