Macro Problem TextToColumns

[Guest]

Below is macro that I “made†using the Tools/Marco/Record New Macro. When I
move to next row (8 36 120 24 180 1,438 13 7,063) and press Ctrl+t I get msg
“Do you want to replace the contents of the destination cells?â€, ok/cancel
and select ok the data is placed in row 8 not current row.
Question – How do I get macro to put data in current/active row? I will move
to next row manually unless there is an easy way to move to next after macro
runs.
Thanks
Rick

Sub Txt2Table()
‘
‘ Txt2Table Macro
‘ Macro recorded 8/7/2007
‘
‘ Keyboard Shortcut: Ctrl+t
‘
Selection.TextToColumns Destination:=Range(“B8â€),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(9, 1),
Array(11, 1), _
Array(14, 1), Array(18, 1), Array(21, 1))
End Sub

Hope I explained problem and what I am trying to do.
Thx
Rick

 

[Guest]

Sub Txt2Table()
‘
‘ Txt2Table Macro
‘ Macro recorded 8/7/2007
‘
‘ Keyboard Shortcut: Ctrl+t
‘
Selection.TextToColumns Destination:= _
Range(“B" & Activecell.row), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), _
Array(6, 1), Array(9, 1), Array(11, 1), _
Array(14, 1), Array(18, 1), Array(21, 1))
End Sub

 

[Guest]

Joel
Thank... I'll it a try.
It looks like something that should work... it seems to make sence.
Thx again
Rick

 

[Guest]

Joel
I tried it be see below for results:
Txt2Col = 10 34 357 24 415 703 59 2,292
Results when macro runs
B16 10
C16 34
D16 357
E16 2
F16 4 4
G16 15 7
H16 3
I16 59 2,292
Columns B C D E & H are right hand oriented like numbers would be
Columns F G & I are left hand oriented like letters would be
Would them all to be numbers
Thx again
Rick

 

[Guest]

re-record (record again) your macro and choose delimited (first dialog)
rather than fixed width with space as the delimiter and select to treat
multiple delimiters as 1 (second dialog)

Make Joel's adjustment to this newly recorded code.

 

[Guest]

Tom-
Thx for help but maybe I need to put comment in my profile to effect that I
have very limited programing knowledge.
I understand once something has been explained/ shown (as in Joel's example).
I will try to figure out what you are saying but I may be posting again for
more info/example.
Rick

 

[Guest]

Tom I re-entered the macro below but gett “Compile Error: Syntax Errorâ€:
Sub TxtToColumns()
'
' TxtToColumns Macro
' Macro recorded 8/7/2007
'
' Keyboard Shortcut: Ctrl+t
'
Selection.TextToColumns Destination:= _
Range("B" & ActiveCell.Row), _
DataType:=xlDelimited, Space:=True
FieldInfo:=Array(Array(0, 1), Array(3, 1), _
Array(6, 1), Array(9, 1), Array(11, 1), _
Array(14, 1), Array(18, 1), Array(21, 1))
End Sub
Get “Compile Error: Syntax Errorâ€