For Loop goes beyond parameter VBA

Question

I did the following for loop to go down each cell until it reaches the cell that is level with the last used cell in another column. However it goes well beyond the last used cell. It works okay, but just takes a lot longer to run especially when it has 30,000 rows to go through! Does anyone have any ideas on this one?

Dim i As Long
lMaxRows = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To lMaxRows
    Range("D" & lMaxRows + 1).Select
    ActiveCell.FormulaR1C1 = "0:00:00"
    lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E" & lMaxRows + 1).Select
    ActiveCell.FormulaR1C1 = "1"
Next i

You probably have data further down than you expect. Also, you should read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on how to avoid select. — Kyle, Jun 24 '16 at 16:05
Consider what you're doing, you're resetting `lMaxRows` in the loop and then you're setting a value in `E & lMaxRows +1`, so you're never going to reach the end of the loop..... — John Bustos, Jun 24 '16 at 16:08
You are re-computing `lMaxRows` on each pass through the loop. — Ron Rosenfeld, Jun 24 '16 at 16:08
Do you expect the `For` loop to update `lMaxRows` as it gets updated within the loop? AFAIK that isn't going to work. Once the `For` line is read, the `lMaxRows` won't change, so it'll go to the max rows originally given. — BruceWayne, Jun 24 '16 at 16:08
Keep in mind that `End(xlUp)` will stop at cells with an empty string like `=""` which often is the result of formulas... if that is the case, then better use `lMaxRows = Cells.Find("*", Range("L1"), xlValues, , , 2, 2).Row`;) — Dirk Reichel, Jun 24 '16 at 16:26

score 5 · Accepted Answer · 2016-06-25T01:03:41.497

5

Define the parent worksheet, avoid Select and fill all of the cells at once.

With Worksheets("Sheet1")
    With .Range(.Cells(1, "K"), .Cells(.Rows.Count, "K").End(xlUp))
        .Offset(0, -7) = "0:00:00"
        .Offset(0, -6) = 1
    End With
End With

edited Jun 25 '16 at 01:03

answered Jun 24 '16 at 16:16

1

Thanks for catching that @ScottCraner. Too many things on my non-SO plate to seemingly bounce in here and solve problems it seems. Edited to fix above. – Jun 25 '16 at 01:05

score 0 · Answer 2 · answered Jun 24 '16 at 16:10

I think it's due to the lMaxRows usage and the loop.

Does this work?

Sub t2()
Dim lMaxRows&, xMaxRows&
Dim i       As Long
lMaxRows = Cells(Rows.Count, "K").End(xlUp).Row
i = 1
Do While i <= lMaxRows
    Range("D" & lMaxRows + 1).FormulaR1C1 = "0:00:00"
    xMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E" & xMaxRows + 1).FormulaR1C1 = "1"
    i = i + 1
Loop
End Sub

Without some sample data, it's kind of hard to know what you want to do/expect to output.

For Loop goes beyond parameter VBA

2 Answers2