Hi Everyone,
I have the macro below that splits the cell content of column D. However, this only works for those values in column D (Destination:=Range("D1")). I have tried a few things but can't get it right, I want the macro to run on any column I choose and not just Column D. Can anybody help?

Sub Split_Cells()

ActiveCell.EntireColumn.Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), TrailingMinusNumbers:=True
End Sub

Thank you

Garry

hi
this is what is restricting it to D.
Destination:=Range("D1")
change to...
Destination:=selection.offset(0,1)

note: make sure that your column next to your target column is blank for
text to columns will overwrite data.

Regards
FSt1

"Garrystone" wrote:


Hi Everyone,
I have the macro below that splits the cell content of column D.
However, this only works for those values in column D
(Destination:=Range("D1")). I have tried a few things but can't get it
right, I want the macro to run on any column I choose and not just
Column D. Can anybody help?

Sub Split_Cells()

ActiveCell.EntireColumn.Select
Selection.TextToColumns Destination:=Range("D1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)),
TrailingMinusNumbers:=True
End Sub

Thank you

Garry




--
Garrystone

Maybe something like:

Option Explicit
Sub Split_Cells()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection.Cells(1).EntireColumn, .UsedRange)
On Error Resume Next
End With

If myRng Is Nothing Then
MsgBox "Nothing to do!"
Exit Sub
End If

myRng.TextToColumns Destination:=myRng.Cells(1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), _
TrailingMinusNumbers:=True

End Sub



Garrystone wrote:

Hi Everyone,
I have the macro below that splits the cell content of column D.
However, this only works for those values in column D
(Destination:=Range("D1")). I have tried a few things but can't get it
right, I want the macro to run on any column I choose and not just
Column D. Can anybody help?

Sub Split_Cells()

ActiveCell.EntireColumn.Select
Selection.TextToColumns Destination:=Range("D1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)),
TrailingMinusNumbers:=True
End Sub

Thank you

Garry

--
Garrystone

--

Dave Peterson

I think it puts the parsed data back in column D--well, that's the way I read
the post.

If that's the case, then maybe:
Destination:=selection

FSt1 wrote:

hi
this is what is restricting it to D.
Destination:=Range("D1")
change to...
Destination:=selection.offset(0,1)

note: make sure that your column next to your target column is blank for
text to columns will overwrite data.

Regards
FSt1

"Garrystone" wrote:


Hi Everyone,
I have the macro below that splits the cell content of column D.
However, this only works for those values in column D
(Destination:=Range("D1")). I have tried a few things but can't get it
right, I want the macro to run on any column I choose and not just
Column D. Can anybody help?

Sub Split_Cells()

ActiveCell.EntireColumn.Select
Selection.TextToColumns Destination:=Range("D1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)),
TrailingMinusNumbers:=True
End Sub

Thank you

Garry




--
Garrystone


--

Dave Peterson

Thanks FSt1
Worked fine, I just needed to alter the offset to 0,0 since in my spreadsheet I am overwriting the initial column.

Thanks again

Garry

Hi Dave
Both of the solutions worked in this post. however, I like the usage of the error and message box, thanks very much.

Garry