Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating macro
I have a document which I want to format for excel. I am converting text to columns. I want it to do one group, go down 3 rows then convert the next 3. I want it to do this all the way to the end of my data. I know it can be accomplished easily, but I can't do it. I am attaching the code and would appreciate any and all assistance.
Sub converttexttocolums() ' ' converttexttocolums Macro ' Macro recorded 12/05/2003 by Michael B. Dean ' ' Keyboard Shortcut: Ctrl+h ' ActiveCell.Range("A1:A3").Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(19, 1), Array(26, 1), Array(39, 1), Array(48, 1), _ Array(71, 1), Array(78, 1), Array(91, 1), Array(104, 1), Array(117, 1), Array(136, 1), _ Array(149, 1), Array(157, 1), Array(168, 1)) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating macro
Michael,
Try this Sub converttexttocolums() Dim cLastRow As Long Dim i As Long cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 4 To cLastRow Step 3 Range("A" & i & ":A" & i + 2).TextToColumns Destination:=ActiveCell, _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(19, 1), Array(26, 1), _ Array(39, 1), Array(48, 1), _ Array(71, 1), Array(78, 1), _ Array(91, 1), Array(104, 1), _ Array(117, 1), Array(136, 1), _ Array(149, 1), Array(157, 1), Array(168, 1)) Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Michael" wrote in message ... I have a document which I want to format for excel. I am converting text to columns. I want it to do one group, go down 3 rows then convert the next 3. I want it to do this all the way to the end of my data. I know it can be accomplished easily, but I can't do it. I am attaching the code and would appreciate any and all assistance. Sub converttexttocolums() ' ' converttexttocolums Macro ' Macro recorded 12/05/2003 by Michael B. Dean ' ' Keyboard Shortcut: Ctrl+h ' ActiveCell.Range("A1:A3").Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(19, 1), Array(26, 1), Array(39, 1), Array(48, 1), _ Array(71, 1), Array(78, 1), Array(91, 1), Array(104, 1), Array(117, 1), Array(136, 1), _ Array(149, 1), Array(157, 1), Array(168, 1)) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating macro
I'll bet this will work even better. Seems like you're
trying to clean up a fixed width data file. Just open it directly into Excel from a .txt or .prn file with this command: Workbooks.OpenText Filename:="C:\Docs\MyData.prn", Origin:=xlWindows, _ StartRow:=1, DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(19, 1), Array(26, 1), Array(39, 1), Array(48, 1), _ Array(71, 1), Array(78, 1), Array(91, 1), Array(104, 1), Array(117, 1), Array(136, 1), _ Array(149, 1), Array(157, 1), Array(168, 1)) If you really have to do this row by row... 'Select three cells in one row Range(ActiveCell, ActiveCell.Offset(0, 2)).Select While ActiveCell < "" 'Do your text to column conversion 'Selection.TextToColumns Selection.Font.Bold = True 'move down a row leaving 3 cells selected Selection.Offset(1, 0).Select Wend -- Dory Owen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating macro
I left my little test line of turning text bold in that
code I gave you. Sorry. Ignore that. Here's the code: 'Select three cells in one row Range(ActiveCell, ActiveCell.Offset(0, 2)).Select While ActiveCell < "" 'Do your text to column conversion Selection.TextToColumns...etc... 'move down a row leaving 3 cells selected Selection.Offset(1, 0).Select Wend |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeating macro | Excel Discussion (Misc queries) | |||
macro repeating getting the data | Excel Discussion (Misc queries) | |||
repeating a macro | Excel Discussion (Misc queries) | |||
Repeating Macro | Excel Discussion (Misc queries) | |||
Repeating (Looping) a Macro | Excel Programming |