Repeating macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
I have a document which I want to format for excel. I am converting text
Click to expand...
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),
Click to expand...
Array(117, 1), Array(136, 1), _
 
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
(e-mail address removed)
 
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
 
Back
Top