CODEWITHSUNDEEP
excelvba
fayomo
fayomo

Reputation: 11

Check last row every iteration in a for loop

This is my code :

 For Each cell In Range("D2:D" & LastCompoundInterval)
        LastTransaction = Range("H" & Rows.Count).End(xlUp).Row
        If <some condition>
        ...
        If <some condition>
        ...
        End If
        Range("H" & LastTransaction) = DateAdd("m", m, cell.Offset(0, -2).Value)
        Range("I" & LastTransaction) = 0
        End If
Next cell

I am appending values to the end of the H and I columns, which have the same number of rows, but the LastTransaction variable for the last row remains the same each iteration. Shouldn't it update every iteration?

Upvotes: 1

Views: 527

Answers (3)

JMP
JMP

Reputation: 4467

I think your code should be:

 For Each cell In Range("D2:D" & LastCompoundInterval)
        LastTransaction = Range("H" & Rows.Count).End(xlUp).Row
        If <some condition>
        ...
        If <some condition>
        ...
        End If
        Range("H" & LastTransaction + 1) = DateAdd("m", m, cell.Offset(0, -2).Value)
        Range("I" & LastTransaction + 1) = 0
        End If
Next cell

Previously it only overwrote the value of the last row in the columns, and didn't add another value as another row. + 1 makes the new entry on the next available row.

Upvotes: 0

Storax
Storax

Reputation: 12167

First of all the answer to your question Shouldn't it update every iteration? is Yes. It updates LastTransaction but it just stays the same.

You need to re-calculate lastrow like before the next like that if you want to go to the next row.

 LastTransaction = Range("H" & Rows.Count).End(xlUp).Row  'Maybe you also need to add a +1 here if you want to have the first empty cell in column H
 For Each cell In Range("D2:D" & LastCompoundInterval)
        ' LastTransaction = Range("H" & Rows.Count).End(xlUp).Row
        If <some condition>
        ...
        If <some condition>
        ...
        End If
        Range("H" & LastTransaction) = DateAdd("m", m, cell.Offset(0, -2).Value)
        Range("I" & LastTransaction) = 0
        End If
        LastTransaction = Range("H" & Rows.Count).End(xlUp).Row +1
Next cell

Upvotes: 1

Light
Light

Reputation: 1316

LastTransaction comes from Range("H" & Rows.Count).End(xlUp).Row. It will get the row number of the last non-blank cell in H column.

Rows.Count here means the total row count in the active worksheet, which is a constant. The whole expression is identical to the following manual operation:

  • locate the last cell (with largest row number) in H column
  • search for the next non-blank cell upward by hitting ctrl+up arrow
  • return that cell's row number

Upvotes: 0

Related Questions