0

I'm trying to run "for loop" certain times and occasionally add an extra row in the range being under the loop. Let's say, that example has originally 15 rows, and during the loop, the condition is true 2 times, so it should add +2 to the total number of rows. However, the code doesn't seem to execute the loop for those added rows and exits right after passing the value of orc=15.

Code below:

sub loop_to_orc()
    dim i as integer, orc as integer
    dim operations as range
    set operations = range(cells(1, 1), cells(, 1).end(xldown))
    orc = operations.rows.count

    for i = 1 to orc
        if cells(i,1)>0 then
            rows(i+1).insert
        end if
        orc = operations.rows.count
    next i
end sub

Where am I wrong? Is there any method to actually run the loop for added rows?

Community
  • 1
  • 1
Oskar_U
  • 472
  • 4
  • 13

1 Answers1

0

You could introuduce one additional variable:

Dim rowsAdded As Long
rowsAdded = 1

And then use it like this:

for i = 1 to orc
    if cells(i,1)>0 then
        rows(i+rowsAdded).insert
        i = i - 1
        rowsAdded = rowsAdded + 1
    end if
next i

This way, you won't increment i when rows is inserted, but you select i in a loop, so we have to add rowsAdded.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • _"This will increase by 1 upper bound of iterator"_ No, it won't. VBA doesn't support that. Check the duplicate question. – 41686d6564 stands w. Palestine Jun 03 '18 at 19:09
  • You can visually see why this wont work as well. If you step through the code (f8) you will notice that it jumps from "next i" to your if statement. The UBOUND does not get re-evaluated on each new "for" loop – urdearboy Jun 03 '18 at 20:16