Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |