For-Next loop closing point not updating...

[lopiteaux]

Hi all,

I have written a bit of code that cycles through tabled data and matches certain records with other records in the table. I start from the top using a For-Next loop and for every n in the first For-Next loop I run another loop on the same table in order to find the matching entry.

The code works perfectly, but there is one small problem - for some records I need to add an additional record in the dataset, thus increasing the size of the original dataset. I have added a line to increase the LastRow, which is the closing point for both For-Next loops, but the problem is that this does not update in the actual loop, so it still stops at the original LastRow (e.g. original LastRow was 500 and 50 rows were added by the code, resulting LastRow should be - and is - 550 but the loop still closes at 500).

Sorry for the lack of code - thought it would be unecessary given that the issue is pretty simple. I have obviously thought of switching the current For-Next loop for a Do Until loop, using IsEmpty(x) as the closing point, but I am rather partial to the For-Next structure, not to mention that I'm not that keen to rewrite it all now.

Would anyone be able to give me any insight into the above?

Regards,

l.

 

[CharlesChuckieCharles]

Maybe post the code


Or make the total row count dynamic (reference the range, but its slower) or add 1 to your total rows when you insert a row?

 

[lopiteaux]

Maybe post the code


Or make the total row count dynamic (reference the range, but its slower) or add 1 to your total rows when you insert a row?

Sorry, I must have been a bit vague in my post - there is a line of code that adds 1 to the LastRow when a row is inserted. By running the code step-by-step I can see that this IS updating the LastRow, as expected, but unfortunately this update is not reflected in the initial closing point, i.e. the code still stops once it's reached the original LastRow.

I could post the code but have unfortunately left the office...

 

[lopiteaux]

Maybe post the code


Or make the total row count dynamic (reference the range, but its slower) or add 1 to your total rows when you insert a row?

Charles, how would I go about making the row count dynamic? And do you know whether there is a way for the closing point to update, as you've suggested and I've tried?

Regards,

l.

 

[CharlesChuckieCharles]

I was wrong

Neither For
nor For Each

revisits the size of the loop once started

use a while wend or Do Loop

 

[p45cal]

run upwards through the table which you're adding rows to?
For n=lastrow to 2 step -1
If it's the inner loop, update LastRow within each outer loop.

 

[lopiteaux]

I was wrong

Neither For
nor For Each

revisits the size of the loop once started

use a while wend or Do Loop

Tried and tested it a few times last week, so I thought as much! In the end I modified it all to a Do Loop, as you've suggested above as well, and simply added a line or two to set the active row as the variable, so I was able to keep the original code. In short - for those that encounter the same issue, I changed the code from this:

For a = StartRow to LastRow

	xxx

	For b = StartRow to LastRow

		xxx

	Next b

Next a

To this, which eliminates the problem whilst still retaining the familiar For-Next format:

Cells(StartRow, x).Select

	Do Until IsEmpty(ActiveCell)

		a = ActiveCell.Row
	
		xxx

		Cells(StartRow, x).Select

			Do Until IsEmpty(ActiveCell)

				b = ActiveCell.Row

				xxx

			Loop

	Loop

Thanks again for the help.

l.

 

[lopiteaux]

run upwards through the table which you're adding rows to?
For n=lastrow to 2 step -1
If it's the inner loop, update LastRow within each outer loop.

Have not had the chance to check this one yet, but will do so tomorrow... looks interesting!