First, Did you try Data|Text to columns using delimited by space (treating
consecutive delimiters as one)?

If no, then try it. You may be pleasantly surprised.

And you wouldn't have to do each cell one by one, either.

=========
But...

I put this in A1:
7770-6255-------7340-7250-------7725-8578-------8511-9837-------
11185-9250-9775-1096

(Where each hyphen represents a space character--and there was no line wrap
within A1)

I manually ran data|text to columns|fixed width|just clicked next.

Each number was put into its own cell:
A1:L1 (Transposed for posting)
7770
6255
7340
7250
7725
8578
8511
9837
11185
9250
9775
1096

Is each of these numbers a "segment". I'm not sure what that means.

Do you get different results?

========
If you want to fiddle with just mimicking keystrokes, you may want to look at
sendkeys in VBA's help. I wouldn't use this. I wouldn't recommend others use
it either.


Parusky wrote:

Dear Dave,
The delimiter is sort of moot.

The data string length of the text is different for each row.
Never-the-less, the R2Column function parses it fine using fixed width. The
problem is that the Macro recorder cannot record a function where it assigns
a value to a function.

For example, the following is an example of unparsed data with each segment
equal to one cell:
7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 1096

870 855 735 1050 455 455 420 775 60 0 35 420

116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
The recorded macro to parse these three cells into 12 columns of data
correctly which was generated from the original posting commands is as
follows:

Selection.TextToColumns Destination:=Range("D8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(14,
1), Array(19, 1), _
Array(24, 1), Array(29, 1), Array(34, 1), Array(39, 1), Array(45,
1), Array(50, 1), Array( _
55, 1))
Range("D9").Select
Selection.TextToColumns Destination:=Range("D9"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1), Array(11,
1), Array(16, 1), _
Array(20, 1), Array(24, 1), Array(28, 1), Array(32, 1), Array(35,
1), Array(37, 1), Array( _
40, 1))
Range("D10").Select
Selection.TextToColumns Destination:=Range("D10"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(12, 1), Array(19,
1), Array(26, 1), _
Array(33, 1), Array(39, 1), Array(46, 1), Array(53, 1), Array(60,
1), Array(67, 1), Array( _
74, 1))
End Sub

Note that the cells are not relative when in reality it would be. Each line
has a different array solution based on the data. If I were to run this
macro on another set of data with a different length it does not and cannot
work.

All I wish to do is start on the first cell and duplicate the key strokes of :

ALT D,e
Enter
Enter
Enter

For each cell

Thanks for considering the problem.

Cordially,
PA

"Dave Peterson" wrote:

I don't understand.

If you're depending on excel to guess where the field breaks should be, then it
sure sounds like the data could be parsed by using space as the delimiter.

But maybe I'm missing something.

Parusky wrote:

Dear Dave,

It works in Lotus but not here. I suspect that the macro recorder records
the results of the R2C wizard's evaluation of the data rather than the
keystrokes required to progress through the wizard. The recorded VBA
programs look like specific arrays that are data specific. When the recorded
macro is run against another set of different data the results are not
accurate. The recorder just does not record the keystrokes with Wizards.
My posted keystroke sequence works fine. It just gets tiring after doing it
for the 4 or 500th time in a row.
BTW I can record a macro for each different text string length and run a
macro for each row. This still means I have to key in something for each row.
The idea is to write a VBA program in a relative loop that duplicates the
keystrokes noted.

Pa

"Dave Peterson" wrote:

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson