Stopping A Loop When A Blank Cell Is Reached

Hi All,

I have the following subroutine from a recorded macro:

Please Login or Register  to view this content.

It's doing nothing more than copying data to cells in I14 to P14 and then pasting (autofill) all the data in I14:P14 to row 65 (see screen capture of the spreadsheet below to get a better understanding).

ScreenCap.JPG

I would like to remove the restriction of only pasting to the 65th row and have a loop that would allow the user to paste to as many rows as is required until there is no more data to perform an operation on. i.e. make the loop stop once a blank cell is reached in =RC[-6].

Side note: The macro is using the term =RC[-6] for =C14. What's going on there then?

Best Regards,

Aaron

the "=RC[-6]" is a relative reference to cell C14 if the active cell is I14. The R without a bracketed number indicates to the formula to use the same row and the C[-6] indicates to use the column 6 to the left (left indicated by the minus sign. Plus sign would indicate to the right).

As for the loop, you don't need one. If your data is contiguous you can use column H to indicated how far down to paste your formulas. You can use something like this:

Please Login or Register  to view this content.

to replace the code:

Please Login or Register  to view this content.

Originally Posted by Aaron1978

Hi All,

I have the following subroutine from a recorded macro:

Please Login or Register  to view this content.

It's doing nothing more than copying data to cells in I14 to P14 and then pasting (autofill) all the data in I14:P14 to row 65 (see screen capture of the spreadsheet below to get a better understanding).

Attachment 5332

I would like to remove the restriction of only pasting to the 65th row and have a loop that would allow the user to paste to as many rows as is required until there is no more data to perform an operation on. i.e. make the loop stop once a blank cell is reached in =RC[-6].

Side note: The macro is using the term =RC[-6] for =C14. What's going on there then?

Best Regards,

Aaron