|
Macro Problem TextToColumns
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 |
Macro Problem TextToColumns
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 "WAMTC" wrote: 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 |
Macro Problem TextToColumns
Joel
Thank... I'll it a try. It looks like something that should work... it seems to make sence. Thx again Rick "Joel" wrote: 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 "WAMTC" wrote: 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 |
Macro Problem TextToColumns
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 "Joel" wrote: 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 "WAMTC" wrote: 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 |
Macro Problem TextToColumns
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. -- Regards, Tom Ogilvy "WAMTC" wrote: 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 "Joel" wrote: 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 "WAMTC" wrote: 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 |
Macro Problem TextToColumns
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "WAMTC" wrote: 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 "Joel" wrote: 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 "WAMTC" wrote: 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 |
Macro Problem TextToColumns
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "WAMTC" wrote: 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 "Joel" wrote: 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 "WAMTC" wrote: 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 |
| All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com