Simple VBA

[normpam]

Have a first and last name in cell C10. Recorded a macro to use TextToColumns.

Selection.TextToColumns Destination:=Range("C10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _

I would like to be able to run the macro for any cell, so I changed the "C10" in the first line above to ActiveCell, ActiveCell thinking it would work... but I get an error. What am I missing here?

Thanks!

 

[lrobbo314]

I tested this code out with "A B C" in cell A1. It worked an after the code ran "A" is in A1, "B" is in B1, and "C" is in C1. What error are you getting?

Sub Test()
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False
End Sub

 

[normpam]

Much thanks! It's almost always the syntax that gets me! I thought by replacing the "C10" address with the ActiveCell... it would work. But as you show, the syntax needs to be without the parentheses.

 

[Rick Rothstein]

I tested this code out with "A B C" in cell A1. It worked an after the code ran "A" is in A1, "B" is in B1, and "C" is in C1. What error are you getting?

Sub Test()
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False
End Sub

If the number of "names" in the selected cell is not fixed (as your example suggests), one can use this simpler code line as long as some maximum number of names can be set (here I assume no more than 99 names will ever appear in the selected cell)...

Sub Test1()
  Selection.Resize(, 99) = Split(Selection & Space(99))
End Sub

However, the OP seemed to suggest that there would always only be two names in the cell. If that is always true, then my code can be reduced to this)...

Sub Test()
  Selection.Resize(, 2) = Split(Selection & "  ")
End Sub

Note that there are two space characters between those quote marks.