Solved - For...each, one step back?

[magelan]

Howdy,

So in my last post I discussed finding a cell and checking it to the eventual conclusion that I would be deleting that row.

Now I have a new problem...

Post deletion, it seems as though my For-Each loop doesnt realize the index has changed, and immediately skips a bunch of cells. Here's the code.


'For k = checkRange.Rows.count To checkRange.Row Step -1
For Each cellVar In checkRange
'workingCell = cells(k,checkRange.column)
Call introCheck(cellVar, countryLocation, taxLocation, deletedRow)
If Not deletedRow Then
Call lengthCheck(cellVar)
End If
Next

As you can see, I tried making a backwards-stepping forloop instead of a forwards-running for-each loop but that broke my subroutines. i replaced cellvar with workingCell and each of the subroutines would see that as "object required" instead of the actual cell with all of its information.

Is there a fix/alternate method? Obviously the easiest way would just be to tell the For-Each loop to step backwards one step if deletedrow=true [deletedRow = true if the row was deleted during introCheck]

edit - removed irrelevant code for ease of reading

Edit2 - nevermind ---

changed one line of code to
Set workingCell = Cells(k, checkRange.Columns(1).Column) which gives the right object.

[p45cal]

There is some danger in using
checkRange.Rows.count To checkRange.Row Step -1
because if checkrange does not start in row 1 the wrong cells will be checked.
Say that checkrange was A11:A30
the loop control variable k would start at 20 and step back to 11. Not the 30 to 11 you might hope for.

If checkrange has only one column you could say:
for k = checkrange.rows.count to 1 step -1
set CellVar = checkrange.cells(k)
Call introCheck(CellVar...
...
...
next k

This last introduced another possible error between saying the likes of:
CellVar = checkrange.cells(k)
and:
set CellVar = checkrange.cells(k)

the latter setting CellVar to an object being the cell (same as the For Each..Next loop) while the former usually puts the cell's value into CellVar.

[Teeroy]

For info a way to get the last row in the range to avoid the trap that P45cal has rightly pointed out is Checkrange.Rows(Checkrange.Rows.Count).Row.

[magelan]

For info a way to get the last row in the range to avoid the trap that P45cal has rightly pointed out is Checkrange.Rows(Checkrange.Rows.Count).Row.

very good points - thanks for the logic on how to fix this. Once you mentioned not being in row 1, i already knew I would have a problem since one of the data sheets I am checking starts on row 8. Thanks!