Another Text to Date Question 1

[tav1035]

I have 4 columns of text-dates that I need to convert to excel dates, mainframe type xml exports.
I know using the Text to Columns works for me but I can only do 1 column at a time.
I also recorded it as a macro.
Question: can I do all 4 columns (R:U) using this macro?

    Columns("S:S").Select
    Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

I've also tried this Macro which works too but only on the 1 column that is selected.

Sub MakeTrueDate()
    'Converts Text Dates(US) to dates(US), Tom Ogilvy, 2001-03-24 programming
    Dim rng As Range
    Set rng = Intersect(ActiveCell.EntireColumn, _
    ActiveSheet.UsedRange) 'next assume first row is a header
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
    rng.NumberFormat = "mm/dd/yyyy"
    rng.Value = rng.Value
End Sub

Thanks,
tav

 

[AnotherHiggins]

tav : For future reference, VBA-specific questions should be posted in forum707.

If you have code that is working for one column, then you could just loop through the four columns by wrapping your code like this:

For Each oCol In Columns("A:D")
[tab]'...
[tab]'Your Code
[tab]'...
Next oCol

A faster method might be to do something like:

range("A:D").value = range("A:D").value

If you need any further help with either method, please start a new thread in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tav1035]

anotherhiggins,
The second method worked great.

Range("R:V").Value = Range("R:V").Value
    Dim rng As Range
    Set rng = Intersect(ActiveCell.EntireColumn, _
    ActiveSheet.UsedRange) 'next assume first row is a header
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
    rng.NumberFormat = "mm/dd/yyyy"
    rng.Value = rng.Value

thanks
tav