Reszing a loop - Possible?
 

Code is as follows:

lngLoopLength = .UsedRange.Rows.Count

For lngIndex = 3 To lngLoopLength

"if condition met then
InsertRow
end if

lngLoopLength = .UsedRange.Rows.Count

Next lngIndex

the loop if not resizing using this method.
Ideas anyone?
tia,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=572120

Reszing a loop - Possible?
 

It isn't mentioned in the help, but it appears that the
From, To and Step parameters of a For statement
are only evaluated once. You could do something like:

lngIndex = 3
Do While lngIndex <= .UsedRange.Rows.Count
' do stuff
lngIndex = lngIndex +1
Loop

(which works because the While condition is fully
evaluated every time round the loop)

Andrew


MattShoreson wrote:
Code is as follows:

lngLoopLength = .UsedRange.Rows.Count

For lngIndex = 3 To lngLoopLength

"if condition met then
InsertRow
end if

lngLoopLength = .UsedRange.Rows.Count

Next lngIndex

the loop if not resizing using this method.
Ideas anyone?
tia,
Matt.

Reszing a loop - Possible?
 

sorry forgot to post...
you can also do

for i = 1 to .usedrange.row.count step -1
do stuff
next i

essentially working backwards.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=572120

Reszing a loop - Possible?
 

Matt,
Does this explain why ?
Private Sub CommandButton2_Click()
Dim i As Long
Dim MaxI As Long
Dim Counter As Long

MaxI = 100
For i = 1 To MaxI
MaxI = MaxI + 1
Counter = Counter + 1
Next i
Debug.Print Counter

End Sub

The answer is to loop backwards:
For lngIndex = .UsedRange.Rows.Count to 3 Step -1
and insert after you.

Or change to a Do Until <You Reach the end
depending on how/where your inserts are going.

You can change the number of loops you make with:
For Each Cell In Range("rngData")
but you need be careful you can actually reach the end of the range

NickHK

"MattShoreson"
wrote in message
news:MattShoreson.2cmmkp_1155721204.9799@excelforu m-nospam.com...

Code is as follows:

lngLoopLength = .UsedRange.Rows.Count

For lngIndex = 3 To lngLoopLength

"if condition met then
InsertRow
end if

lngLoopLength = .UsedRange.Rows.Count

Next lngIndex

the loop if not resizing using this method.
Ideas anyone?
tia,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=572120

Reszing a loop - Possible?
 

Nick - not too sure what you mean in your post.

My reasoning by my post is that...

instead of moving forward through the loop and then resizing it t
ensure that all cells in the usedrange are covered (they increase du
to the insertrow method).

Set the looplength to start with and process from the end to the start
thereby making redundant the need to resize with inserting of rows.

what may not be apparent from the orginal post is the inserting of row
is directly relational to the position in the loop.
i.e. .Cells(lngLoopLength + 1, 1).EntireRow.Insert shift:=xlDown

I think this makes sense

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=57212

Reszing a loop - Possible?
 

Matt,
I was just trying to show that using a For i=1 to SomeVariable structure,
you can't change the number iterations once you begin. Which is what were
trying to do.
You have to use another method, either by allowing for the change in range
size or organize your code so it is not a concern.
As you have discovered.

NickHK

"MattShoreson"
wrote in message
news:MattShoreson.2cmqie_1155726305.6477@excelforu m-nospam.com...

Nick - not too sure what you mean in your post.

My reasoning by my post is that...

instead of moving forward through the loop and then resizing it to
ensure that all cells in the usedrange are covered (they increase due
to the insertrow method).

Set the looplength to start with and process from the end to the start,
thereby making redundant the need to resize with inserting of rows.

what may not be apparent from the orginal post is the inserting of rows
is directly relational to the position in the loop.
i.e. .Cells(lngLoopLength + 1, 1).EntireRow.Insert shift:=xlDown

I think this makes sense!


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=572120