Test to column not working.

[vmjan02]

I am not sure this is a recorded code and if I try to execute it its not working, not sure why. It has no effect on the cells. I am trying to convert date from 17/06/22 to 17-06-2022

Sub Macro3()
'
' Macro3 Macro
'

    Range("E9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("$E$9:$E$32"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("E56").Select
    Selection.End(xlUp).Select
End Sub

 

[MARK858]

What happens with the code below?

Sub vmjan02()

   Range("E9:E32").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=False
   Range("E9:E32").NumberFormat = "dd-mm-yyyy"
End Sub

 

[vmjan02]

Thanks Mark this is working fine and perfect, thanks and guiding me out.

 

[MARK858]

You're welcome

Just for your reference your issue was FieldInfo:=Array(1, 1), the first 1 is fine as it is a reference to the column. The range is a single column so it can only be 1.
The issue is the 2nd 1. This is the Datatype. When you are doing a conversion you need to match what you see in the cell, in this case it is a date in dd/mm/yyyy format.
The 1 you were using is the General format datatype (xlGeneralFormat) whereas I used FieldInfo:=Array(1, 4), the 4 is the datatype to use for dd/mm/yyyy format (xlDMYFormat)