Increment final value in for statement
 

Can anyone help me please with this macro.

If I add a row to a spreadsheet I want to increment the final value in a for
statement but it doesn't seem to work.

ie

For i = 4 To lastrow

(if statement which can add a row)
lastrow = lastrow + 1
Else: End If
next i


When I run this lastrow doesn't increment and last row stays at the original
value.

Thanks

Ian

Increment final value in for statement
 

Hi Ian,

When the For staement is executed, the current value value of lastrow is
retained as a counter for i for the whole of the For-Next loop.

Try using a counter in DO - LOOP UNTIL lastrow counter.

Regards,
Don

"nicoll" wrote in message
...
Can anyone help me please with this macro.

If I add a row to a spreadsheet I want to increment the final value in a
for
statement but it doesn't seem to work.

ie

For i = 4 To lastrow

(if statement which can add a row)
lastrow = lastrow + 1
Else: End If
next i


When I run this lastrow doesn't increment and last row stays at the
original
value.

Thanks

Ian

Increment final value in for statement
 

Hi Ian,
If you are running through a range and inserting or deleting
rows you want to start from the bottom. Otherwise, the lastcell is not
the only problem you would have -- you would also be skipping rows
or processing rows twice.

For i = lastrow to 4 step -1
....
next i

For more information on inserting and deleting rows and maintaining
your formulas see
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nicoll" wrote in message ...
Can anyone help me please with this macro.

If I add a row to a spreadsheet I want to increment the final value in a for
statement but it doesn't seem to work.

ie

For i = 4 To lastrow

(if statement which can add a row)
lastrow = lastrow + 1
Else: End If
next i


When I run this lastrow doesn't increment and last row stays at the original
value.

Thanks

Ian

Problem solved Increment final value in for statement
 

Thanks Don's answer solved the problem.