Syntax help needed in TextToColumns Macro 1

[PeterMoran]

Hi,

I have the following code at the start of a Text To Cols Macro:

Selection.TextToColumns Destination:=Range("B3"), _

How do I change the Range parameter so that it is variable, and points to the cell to the right of the current selection?

Have searched Help without much luck.

Selection.TextToColumns Destination:=Range(Offset(0,1)), _

is one of my unsuccessful efforts, if you see what I mean!

Any assistance greatly appreciated.

 

[SkipVought]

hi,

'How do I change the Range parameter so that it is variable, 
'and points to the cell to the right of the current selection?
    [b]With Selection
        [highlight #FCAF3E].[/highlight]TextToColumns _
            Destination:=[highlight #FCAF3E].[/highlight]Offset(0, 1), _[/b]
            DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(3, 1)), _
            TrailingMinusNumbers:=True
    [b]End With[/b]

Of course, the DataType, FieldInfo array and TrailingMinusNumbers properties must be modified to suite your particular needs.

FYI the [highlight #FCAF3E].[/highlight] refers to the With object, which in this case is the Selection.

The Offset to the right (0,1) works because in TextToColumns, only ONE COLUMNS is selected. If, in some other circumstance, you were to select multiple columns, a simple Offset would overwrite data in the first row of your selection. You would need to supply more information to put data in "the cell to the right of the current selection.

 

[PeterMoran]

Hi Skip,

Many thanks.

Did the trick perfectly!

Peter Moran