Looping 1

[LouLBI]

Sub LoopRg()
'
' LoopRg Macro
' Macro recorded 6/9/2012 by Lou
'

'
Dim ColStart As Integer
Dim ColEnd As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer


'Define your range start and end
ColStart = 6: ColEnd = 6
'x = the number of ranges to go through
For x = 0 To 8
Range(Cells(ColStart + 10 * x, 1), Cells(ColEnd + 10 * x, 1)).Select
  ColStart = ColStart: ColEnd = ColEnd
  Selection.TextToColumns , DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(25, 1), Array(53, 1), Array(55, 1), _
        Array(58, 1), Array(62, 1), Array(66, 1), Array(70, 1), Array(74, 1), Array(76, 1), Array( _
        77, 1)), TrailingMinusNumbers:=True

Next
ColStart = 7: ColEnd = 7
For y = 0 To 8
Range(Cells(ColStart + 10 * y, 1), Cells(ColEnd + 10 * y, 1)).Select
  ColStart = ColStart: ColEnd = ColEnd

        Selection.TextToColumns , DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(15, 1), Array(53, 1), Array(55, 1), _
        Array(58, 1), Array(62, 1), Array(66, 1), Array(70, 1), Array(74, 1), Array(86, 1), Array( _
        95, 1)), TrailingMinusNumbers:=True
Next
ColStart = 9: ColEnd = 9
'z = the number of ranges to go through
For z = 0 To 6
Range(Cells(ColStart + z, 1), Cells(ColEnd + z, 1)).Select
  ColStart = ColStart: ColEnd = ColEnd
        Selection.TextToColumns , DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(16, 1), Array(21, 1), Array(24, 1), _
        Array(30, 1), Array(34, 1), Array(36, 1), Array(38, 1), Array(40, 1), Array(43, 1), Array( _
        45, 1), Array(48, 1), Array(50, 1), Array(53, 1), Array(60, 1), Array(66, 1), Array(70, 1), _
        Array(86, 1), Array(90, 1), Array(101, 1), Array(112, 1), Array(123, 1)), _
        TrailingMinusNumbers:=True
Next


End Sub

I Created the above looping macro, and the first two loops executed fine, but the third loop interrupted me with the query "do you want to replace the contents of the destination cells?"
I reply "yes", and it goes to the next part of the loop and asks the same question. This is repeated throughout the loop.
Thanks in advance for your help.

 

[PHV]

You may play with the Application.DisplayAlerts property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LouLBI]

My apologies to the forum. I had some cells in the spreadsheet that were occupied.
Loulbi

 

[Andrzejek]

You got your problem solved, but why do you have:
[tt]
ColStart = ColStart: ColEnd = ColEnd
[/tt]
???

Have fun.

---- Andy

 

[LouLBI]

Andy
In the learning mode, I was trying to figure out how to loop and I found this example published on the website

http://www.mrexcel.com/archive/VBA/6681.html

And modified it until it worked for me. It had that statement in it and I kept it, but at my stage of knowledge, I don't know what it does.
Thanks for your interest.
Loulbi

 

[Andrzejek]

That's OK, if you are learing
It does not do anything. You just assign the value of the vatiable to itself (again). So you don't really need this line, IMHO.

In the link you provided you do have a line:
[tt]
ColStart = ColStart + 1: ColEnd = ColEnd + 1
[/tt]
but that's different, you increase the value this way
by 1

Have fun.

---- Andy