Delimited Text-to-Columns in a Macro

**Jollyoli** · 09-28-2017, 06:45 AM

Hello,
I have a reporting tool where all data sets produce dates as general. I need to manually change these for pivot etc.

I am looking for a cure all macro to run as reports may have more than one date column and it will be in a different position on each report.
I want to find all "dates" and reformat them so I can use them for grouping in pivots.

I think my issue is in the destination range section however any input or alternate methods would be gratefully received.

My current VBA -

Sub FormatDate()

 For Each c In ActiveCell.CurrentRegion.Cells
  If IsDate(c.Value) Then Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
  Next

End Sub

Thanks

**JOHN H. DAVIS** · 09-28-2017, 08:28 AM

Maybe:

A different approach?

Sub Jollyoli()
Dim i As Long, x As Long
For i = ActiveSheet.UsedRange.Columns.count To 1 Step -1
    x = Cells(Rows.count, i).End(3).row
        If IsDate(Cells(2, i)) Then Range(Cells(2, i), Cells(x, i)).NumberFormat = "m/d/yy;@"
Next i
End Sub

Please read Forum Rules No. 3. When posting code to the Forum use code tags.

**Jollyoli** · 09-28-2017, 09:21 AM

Hi John,
I gave this a go and not to sound like an ingrate however formatting does not cut it.
The only way to get the output into a form that allows me to do grouping in pivots is through the Text to Columns function.
Thanks for your efforts.

**Sintek** · 09-28-2017, 08:29 AM

Hi jollyoli

Welcome to the Forum.
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

**JOHN H. DAVIS** · 09-28-2017, 12:13 PM

Can you attach a file with some sample data? Preferably with a sheet showing "BEFORE" and one showing "AFTER or RESULTS"?

**Jollyoli** · 09-29-2017, 03:56 AM

Find example attached.
'Raw Export' is direct feed from report.
'Pivot Error' is screen shot of what happens when I try to group a pivot on date from the 'Raw Export' data.
'Data Fixed' is after doing text to columns.
'Grouped Pivot' is an example of a working grouped pivot from the 'Data Fixed' data.

To add annoyance the Raw Export data recognises dates enough to use date related formulas however my end user group all use pivots to analyse their data sets.

Thank you.