Determine loop control based on last row

Question

This code stops part way through the column even though data exist further down with no blank cells in between.

Dim LastcRow, LastqRow, lastdtyperow, lastdqtyrow, lastqtyperow
Dim Dst
Dim Foot
Dim c
Dim cRange As Range
'defines sheets for ease of use and less confusion
Dim dataentry As Worksheet, quote1 As Worksheet
Dim typ As Range
    
Set dataentry = Sheets("Data Entry")
Set quote1 = Sheets("Quote")
LastcRow = Sheets("Data Entry").Range("B1").End(xlDown).Row
LastqRow = Sheets("Quote").Range("C1").End(xlDown).Row
lastdtyperow = dataentry.Range("B1").End(xlDown).Row
lastdqtyrow = dataentry.Range("C1").End(xlDown).Row
lastqtyperow = quote1.Range("A1").End(xlDown).Row
Set typ = dataentry.Range("B9:B" & lastdtyperow)
    
'this needs some work
Foot = Sheets("DATA VALIDATION").Range("I2").Value
    
'this is where the code for copying data begins
Dim i As Integer
Dim cell As Range
    
For i = 0 To lastdtyperow
    'copies types
    Set cell = dataentry.Range("B9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("A13").Offset(i, 0) = cell.Value
        quote1.Range("A13").Offset(i, 0).HorizontalAlignment = xlCenter
        quote1.Range("A13").Offset(i, 0).VerticalAlignment = xlCenter
    End If
    'copies quantities
    Set cell = dataentry.Range("C9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("B13").Offset(i, 0) = cell.Value
        quote1.Range("B13").Offset(i, 0).HorizontalAlignment = xlCenter
        quote1.Range("B13").Offset(i, 0).VerticalAlignment = xlCenter
        quote1.Range("B13").Offset(i, 0).NumberFormat = "#,##0"
    End If
    'copies mfr
    Set cell = dataentry.Range("AB9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("C13").Offset(i, 0) = cell.Value
        quote1.Range("C13").Offset(i, 0).HorizontalAlignment = xlCenter
        quote1.Range("C13").Offset(i, 0).NumberFormat = "#,##0"
        quote1.Range("C13").Offset(i, 0).WrapText = True
        quote1.Range("C13").Offset(i, 0).VerticalAlignment = xlCenter
    End If
    'copies cat number
    Set cell = dataentry.Range("AC9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("D13").Offset(i, 0) = cell.Value
        quote1.Range("D13").Offset(i, 0).HorizontalAlignment = xlCenter
        quote1.Range("D13").Offset(i, 0).WrapText = True
        quote1.Range("D13").Offset(i, 0).VerticalAlignment = xlCenter        
    End If
    'copies notes
    Set cell = dataentry.Range("AD9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("E13").Offset(i, 0) = cell.Value
        quote1.Range("E13").Offset(i, 0).HorizontalAlignment = xlCenter
    End If
    'copies prices
    Set cell = dataentry.Range("AJ9").Offset(i, 0)
    If Not IsEmpty(cell) Then
        quote1.Range("F13").Offset(i, 0) = cell.Value
        quote1.Range("F13").Offset(i, 0).HorizontalAlignment = xlRight
        quote1.Range("F13").Offset(i, 0).VerticalAlignment = xlCenter
        quote1.Range("F13").Offset(i, 0).NumberFormat = "$#,##0.00"
        quote1.Range("F13").Offset(i, 0).Font.Bold = False
    End If
Next i

The For i = 0 is my copying loop that is not working as intended.

This code is my "control" for subsequent code. It determines if i needs to increase and the process be repeated. Once it hits the last cell with data in column B on dataentry, the code is supposed to stop and move to the next portion of sheet generation.

I tried changing the range of lastdtyperow, change xlDown to xlUp and other various odds and ends. If anything, changing ranges and xldown/xlup made the issue worse where it would stop even sooner and some changes caused Excel to lock up/crash.

Side note, but do you really have the letter `o` in `For i = o To lastdtyperow`? Main note: see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. Then use a `For Each` loop and loop over each cell, instead of using `Offset`. — BigBen, Aug 17 '20 at 17:28
Whoops. That was a typo from just before I copied the code over. That has been corrected. I will test this your response and see what happens! — Tmyers, Aug 17 '20 at 17:46
Your code is missing information. `i` could "stop incrementing" for many reasons, and none of them can be diagnosed from just the code you supplied. Include the relevant declarations and keywords in the code instead of telling us your real code has them! we're not seeing the declaration for that `lastdtyperow` variable (is it `Long` or `Integer`, or is it not declared anywhere at all?); does your real code say `On Error Resume Next` anywhere? If so, remove that end [edit] with the actual error you're getting (Overflow?). — Mathieu Guindon, Aug 17 '20 at 17:50
That said finding the last row is usually more reliable with `lastdtyperow = dataentry.Range("B" & dataentry.Rows.Count).End(xlUp).Row`. — Mathieu Guindon, Aug 17 '20 at 17:51
I will edit and supply the full code, but its a doozy. I have not fully fleshed out my coding for breaking things apart into their own modules and such as of yet. — Tmyers, Aug 17 '20 at 17:57
The idea isn't to get the full code, but a [mcve] that produces the same problem and is useful to other people looking for a solution to a similar problem - see [ask] for more details. — Mathieu Guindon, Aug 17 '20 at 17:59
`Dim i As Integer` declares `i` as a 16-bit signed integer type; if you have more than 32,767 rows then when `i` gets assigned to 32,768 you get run-time error 6 "overflow" ...unless `On Error Resume Next` is hiding that bug - then `i` would "stick" to 32,767 and stop incrementing - which sounds like the problem you're describing. What is the value of `i` when execution stops? Have you tried setting breakpoints (F9) and stepping through the code line by line (F8) to see if everything is as expected? — Mathieu Guindon, Aug 17 '20 at 18:04
Yes, as I step through the lines the code runs flawlessly until ```i``` hits either 8 or 9 (I can run it again if that is super important), it stops increasing like there is no more data and the code moves to the next step. My actually data set ends at row 250 and what we use it for, should never get close to or go beyond that. — Tmyers, Aug 17 '20 at 18:08
@Warcupine, i just ran it again and ```lastdtyperow``` topped out at 8 as well, which would explain why ```i``` stops increasing. so second assumption that the problem laid with ```lastdtyperow``` seems to be right. I would like to add that I do not get an error, it just stops at 8 and data that should be copied does not get copied. — Tmyers, Aug 17 '20 at 18:15
Try this instead of what you have ```lastdtyperow = dataentry.Cells(dataentry.Rows.Count, 2).End(xlUp).Row``` — Warcupine, Aug 17 '20 at 18:20
@Warcupine that worked in the sense that it continued and grabed the rest of the data, however now it does not stop until it 250 and copies every line. This my be because those particular cells have formulas in them that are returning zeroes. The formulas go all the way down to row 250 are are =IF(ISBLANK(Counts!A6),"",Counts!A6). Is the fact those formulas are they is why it continued even though the cells are technically blank? — Tmyers, Aug 17 '20 at 18:30
Are they all formulas and you want to ignore blanks or do you want to ignore the formula part? Yes it will pick up formulas as they are technically not empty cells. — Warcupine, Aug 17 '20 at 18:34
Every cell in the relevant range ```dataentry B9-250``` has that formula in it tying it to the other sheet in the workbook. I would need it to ignore formulas that are not returning any value (as they should not return a zero due to the ```ISBLANK``` if im not mistaken) — Tmyers, Aug 17 '20 at 18:37

Warcupine · Accepted Answer · 2020-08-17T18:49:57.560

Based on this:

i just ran it again and lastdtyperow topped out at 8 as well, which would explain why i stops increasing. so second assumption that the problem laid with lastdtyperow seems to be right. I would like to add that I do not get an error, it just stops at 8 and data that should be copied does not get copied.

– Tmyers

Your code is not grabbing the real last row. Using Range("B1").end(xldown) is pretty unpredictable. It will stop at any empty row (formulas and whitespace will be picked up) and it looks like it also stops after table headers.

A better approach is to use .cells instead of .range and also xlup instead of xldown.

lastdtyperow = dataentry.Cells(dataentry.Rows.Count, 2).End(xlUp).Row

Should grab the proper last row.

Don't forget to use proper referencing in front of .Rows or else it will use the active sheet. Or use a with:

With dataentry
    .Cells(.Rows.Count, 2).End(xlUp).Row
end with

As mentioned by Mathieu Guindon in comments don't declare as integer instead use long There is actually no benefit to integer in VBA just always use long.

If you want to get only cells with a value that also have a formula you can use something like this:

dataentry.Columns("B").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

Or add in an if in the loop to ignore cell.value = ""

Worked wonderfully! I just need to make some tweaks to correct the overall base and it should work flawlessly. — Tmyers, Aug 17 '20 at 18:56

Determine loop control based on last row

1 Answers1