Choice of Loop When The Loop Grows?

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
My apologies for the title ... I'm not sure how to best sum up the issue with the proper terminology.
Without getting into the details of a solution, let me explain the situation I got myself into.

I have an application that creates an initial dataset. Lets say the project created a range of 21 rows. eg. nRows = 21
The application then loops (For/Next) through each of the rows of the dataset. ie For i = 1 to nRows in the dataset
Within the loop, code will analyze, compile and adjust this data. AT one point in the analysis, if a certain scenario exists, the code will add rows to the dataset. The dataset has grown from the original 21 rows to 24 with the addition of 3 more rows added by the routine. With each additional row I have been adding one to nRows.

The problem I'm running into as you all will likely see, is my loop will no longer loop through the entire dataset as it has grown. Although nRows has increased to 24, the loop will only loop through the original 21 rows assigned at the establishment of the loop.

My question is, before I ask how, can this situation be easily overcome? What is the best way to adjust a loop to accommodate it's growth? Perhaps an For/Next loop isn't the best choice.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I have some doubts, but in advance I tell you that it is not necessary to increase the final counter.

AT one point in the analysis, if a certain scenario exists, the code will add rows to the dataset.
New records must also be analyzed?

I have an application that creates an initial dataset. Lets say the project created a range of 21 rows.
In that data are there formulas in the cells?

If there are no formulas, then it is simpler. You can load the data into a matriz, process the matriz of 21 records (following your example), and return 24 records to the sheet.

If you need help with the code to process everything in memory with a matriz, you should put your current code, which is the scenario where a new record is created. How many records can the base grow, that is, can each record grow one or two or how many times can each record grow.
In addition to putting a minisheet of your original data and another minisheet with the result.
 
Upvote 0
Hello Dante, thank you for offering your help. I think it might be challenging for me to provide what you need to help find a solution, and even more challenging for others to follow. It involves a lot of calls and userforms so it's going to be a lot of code and will be difficult to follow. It's late so it's something I will have to work on tomorrow. In the meantime, have a look at this thread. Kevin provided me the direction I needed to insert the new rows of data to the previously created base data. It shows the structure of the dataset.

In that example, the dataset starts at Row 13, and originally ended at 33 (disregard the row at 34 for now. The loop I have steps through rows 13 to 34 and processes the data (populates the cells in columm B and H:Q). In that process, it may identify the need to add an additional row. In this case, and it's not illustrated, once I got the code working, an extra row of data was inserted at 31. The dataset increased by one row, so, as a result the loop never made it to the last row where A# equalled 44779008.

I'm not sure if that helps you get some familiarity, but it's a start.
 
Upvote 0
See if this gives you any ideas:
VBA Code:
Sub LoopInsertingRow()

    Dim i As Long, lastRow As Long
    
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    i = 1
    Do Until i >= lastRow
        Debug.Print Cells(i, "C").Address; Cells(i, "C").Value; i
        If i = 10 Then
            Cells(i, "C").EntireRow.Insert
            lastRow = lastRow + 1
            ' --- if action required on inserted row do that here ---
            
            ' Point of insertion - row moved down one row - skip that row
            i = i + 1
        End If
        i = i + 1
    Loop

End Sub
 
Upvote 0
with this kind of problem, if possible, you should think in reverse, start at the last row and go up to the first row, leaving the inserted rows behind you.
 
Upvote 0
Hi folks.
Alex, I will certainly look at how your suggestion works and figure out how/where I could integrate it into my code for testing.
Bsalv, I see your logic, however, the new rows of data can be inserted at any point in the dataset, before or after the row the code stopped at for programming. Your suggestion I can see working if all the new rows followed the last of the rows in the dataset analyzed.

I'm starting to think now that the easiest solution could be to just add any new rows at the very end of the data set. After all the original dataset rows have been processed and any new rows created based on that loop, then integrate the new rows into their respective spots in the main dataset. ie complete the loop then integrate any new rows, that way the original dataset doesn't grow.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top