Excel Macro Question

[Great Blue]

Hi-

I am trying to record a simple macro that will:
- Run the "Text to Column" command on the selected cell
- Split the contents of the cell based on the space delimiter
- Deliver the results to the selected cell and subsequent cells to the
right.

When I run the recorded macro, it delivers the results to the cells
where it was originally recorded. I have pasted the text of the macro
below:

Sub Split_Space()
'
' Split_Space Macro
' Macro recorded 09/08/2003 by GBHE
'

'
Selection.TextToColumns Destination:=Range("B170"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1))
End Sub

 

[J.E. McGimpsey]

Hi-

I am trying to record a simple macro that will:
- Run the "Text to Column" command on the selected cell
- Split the contents of the cell based on the space delimiter
- Deliver the results to the selected cell and subsequent cells to the
right.

When I run the recorded macro, it delivers the results to the cells
where it was originally recorded. I have pasted the text of the macro
below:

Sub Split_Space()
'
' Split_Space Macro
' Macro recorded 09/08/2003 by GBHE
'

'
Selection.TextToColumns Destination:=Range("B170"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1))
End Sub

You need to change the Destination to the selection:

Public Sub Split_Space()
With Selection
.Columns(1).Cells.TextToColumns _
Destination:=.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
End With
End Sub

Destination, by default is the top cell of the selection, so you
could leave it out. Also, since you're FieldInfo sets columns one
and two to General (the default), you needn't include that either if
you don't want to.

 

[Great Blue]

Thanks. That did the trick. Here are few follow up quesitons though:

Public Sub Split_Space()
With Selection

Is this syntax "With Selection" necessary or is it simply descriptive?

.Columns(1).Cells.TextToColumns _
Destination:=.Cells(1), _

Is this a general way to say "the currently selected cell"? I run into
this often where the Macro records the absolute cell rather than a
relative one.

DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
End With
End Sub

Destination, by default is the top cell of the selection, so you
could leave it out. Also, since you're FieldInfo sets columns one
and two to General (the default), you needn't include that either if
you don't want to.

Thanks again.

 

[J.E. McGimpsey]

Here are few follow up quesitons though:


Is this syntax "With Selection" necessary or is it simply descriptive?

No. The With structure allows you to use shorthand to refer to the
object following the With , in this case, Selection.. You use the
shorthand by referring to the object with a ".", so that

is equivalent to

Selection.Columns(1).Cells.TextToColumns _
Destination:=Selection.Cells(1), _

The advantage here is that the object only has to be referenced
once. Referencing an object is a fairly slow process, so using
With...End With makes your code more efficient any time you're
making at least two reference calls to the same object. See
With...End With in VBA Help.

Is this a general way to say "the currently selected cell"? I run into
this often where the Macro records the absolute cell rather than a
relative one.

I wrote the code so that the user could have a range of cells
selected.

Selection.Columns(1).Cells

refers to all of the cells in the first column of the selection.
Likewise,

Destination:=.Cells(1)

refers to the top-left cell in the Selection as the destination.
Note that the top-left cell of a Selection is not necessarily the
activecell - tabbing through a Selection leaves the Selection the
same, but the activecell changes.

Thus if the user has A1:J30 selected when the code is run, the macro
will attempt to split A1:A30.

If you want to limit the macro to the top-left cell in the
Selection, replace

.Columns(1).Cells.TextToColumns _

with

.Cells(1).TextToColumns _