I would imagine that if you vary from the General format, then you'll want to
specify each field.

Otto Moehrbach wrote:

Dave
Thanks for your response. I played around with what I had (before I saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it less
than 7 or more than 7. Now my question is what does the macro have to have
regarding that array and what does it not need? Thanks again for your time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell. So
you
could just build an array with your requirements. But it looks like your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

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



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have anything
from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

--

Dave Peterson