Stop for loop at certain condition

[atomichybrid]

Hi all,

I was wondering is it possible to exit a 'for loop' near the end of a page break? The idea is that when I insert new rows the formulas don't get implemented, I know I can just drag down the cell with the formula and the problem will be solved. However there are a lot of rows and multiple columns with and I rather not go through that mundane task of dragging down cells with formulas, so I would like to create a macro to insert the formulas if it is possible.

I'm open to suggestions.

Cheers

[Cerbera]

You could have code to select the full height of a column and fill in a formula, e.g.

Please Login or Register  to view this content.

Or you could select the first cell in the column with the formula in and fill that down, e.g.

Please Login or Register  to view this content.

You could repeat this for the relevant columns.

Really could do with seeing the sheet to know exactly what's needed.

[atomichybrid]

Thanks Cerbera for your quick response.

Formula _sample.xlsx

I have uploaded a sample excel very basic but the idea should be the same.

I just want the insertion of the formulas till the end of the page break.

Cheers

[Cerbera]

Hi would you want the formula to stop at the page break, even if the data in the first columns carried on further? Or do you just want the formulas to fill down to the same point as the data in the first cells?

[atomichybrid]

Yes I would like the formula to continue even if there are nothing in the first 2 columns, the objective is to continue before the page break. I also want this to work on multiple columns so that it would save me some time.

[Cerbera]

Here's some sample code for column C:

This works for the same number of cells as used in column A - up to the page break:

Please Login or Register  to view this content.

This one sounds like what you are after:

Please Login or Register  to view this content.

You can duplicate the main part of the code changing the "Range" to the appropriate column and the formula to match what you are after.

BTW You can comment out or remove the message box, I just used it to show that it had "found" a page break.

[atomichybrid]

Cerbera you are a legend! Works like a charm, also I failed to mention, can I have this offset from the page break so in other words it will stop like 1 or 2 cells away from the break line. Some times I put comments below the ranges.

Also note I used the second code.

Cheers

[Cerbera]

For sure:

Please Login or Register  to view this content.

I haven't tried the code as not at my pc - changing the value of "j" should change the number of rows to leave at the end.

BTW: if this solves the post, please mark as solved under thread tools at the top. And if you're happy to do so please click the "* Add Reputation" button at the bottom of one of my posts

[atomichybrid]

Cerbera,

You did it again, thanks for the help.