How to do "Text to Columns" on several columns at once.

[leonlai]

Hi, everybody

I obtained the foll. code from a book by a well-known Excel MVP and author.
So, I found it a bit strange why it does not work.
I copied the code from both its web-site and from the book.

Sub Macro47()

[I]'Step 1:  Declare your variables[/I]
    Dim MyRange As Range
    Dim MyCell As Range
    
[I]  'Step 2:  Save the Workbook before changing cells?[/I]
    Select Case MsgBox("Can't Undo this action.  " & _
                        "Save Workbook First?", vbYesNoCancel)
        Case Is = vbYes
        ThisWorkbook.Save

        Case Is = vbCancel
        Exit Sub
    End Select
    
[I]'Step 3:  Define the target Range.[/I]
    Set MyRange = Selection
        
[I]'Step 4:  Start looping through the range.[/I]
    For Each MyCell In MyRange
    
[I]'Step 5:  Reset the cell value.[/I]
    If Not IsEmpty(MyCell) Then
    MyCell.Value = MyCell.Value    [B]'// ?????????[/B]
    End If

[I]'Step 6: Get the next cell in the range[/I]
    Next MyCell


End Sub

The macro is supposed to perform Text to Columns on multiple columns (which cannot be done using Excel menu).

I found Step 5 a bit strange, but the author explains:
"You then simply reset the cell to its own value. This removes any formatting mismatch"


Can anybody spot if there is anything wrong with the code?

Thanks
Leon

 

[James006]

Hello,

The macro you have posted ...

will NOT perform Text to Columns on multiple columns ...

FYI ... The Step 5 transforms a formula in its result ...

 

[James006]

Hello,

You can test following macro :

Sub Multi_Columns_Text_To_Columns()
' Source :
' http://www.databison.com/vba-code-multiple-column-text-to-column-conversion/


Dim selected_range, selected_range_individual_column() As Range
Dim one_to_how_many_columns, col_count As Long


Set selected_range = Selection
On Error GoTo err_occured:


'-------------------------------------------------------------------------------------
'one_to_how_many_columns value  = Number of colums that a single column should be split into
'Provide a sufficiently large value so as to prevent overlaps and overwriting
'-------------------------------------------------------------------------------------
one_to_how_many_columns = 10




Application.DisplayAlerts = False
If Not (TypeName(selected_range) = "Range") Then End
ReDim selected_range_individual_column(selected_range.Columns.Count - 1) As Range


For col_count = LBound(selected_range_individual_column) To UBound(selected_range_individual_column)
    Set selected_range_individual_column(col_count) = selected_range.Columns(col_count + 1)
Next col_count


'Begin Text to Column conversion process by starting from Right and proceeding left
For col_count = UBound(selected_range_individual_column) To LBound(selected_range_individual_column) Step -1


    If Application.WorksheetFunction.CountIf(selected_range_individual_column(col_count), "<>") = 0 Then GoTo next_loop:


    selected_range_individual_column(col_count).TextToColumns _
    Destination:=selected_range.Cells(selected_range.Row, one_to_how_many_columns * col_count + 2), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=True, _
    Other:=False, _
    FieldInfo:=Array( _
        Array(0, 1), _
        Array(3, 1), _
        Array(6, 1), _
        Array(12, 1), _
        Array(17, 1) _
        ), _
        TrailingMinusNumbers:=True
        
next_loop:
Next col_count
err_occured:
Application.DisplayAlerts = True
End Sub

Hope this will help

 

[leonlai]

Hi, James006

Thanks for your reply.

Your code works and I could modify it to suit my requirements in actual projects.

However, my question was just an academic one.

Is there no way to modify the single line in the book example to make it work?

Seemed too good to be true!

Best Regards,

Leon

 

[James006]

Hello,

As already explained ...

The macro you have posted ... will NOT perform Text to Columns on multiple columns ...

The macro posted will transform a formula in its result ...

So ... my conclusion is : there is a major Error in your Book ... !!!

 

[leonlai]

Thanks again, James006

Leon

 

[James006]

You are welcome ...