for each row or do while construct

[Conor]

Hello,

Haven't been here in a long time, but need some help now. I'm using Excel 2002 for this project.

I am using a "for each row" construct because I am fairly familiar with it. A "do while" may be better, but I don't know how to process rows with it.

I am reading a named range on an order worksheet and writing the items with ship qtys to the invoice worksheet, which for printing purposes is limited to 22 lines. Hence the "goto" command. (Not great code, but I'm stuck here.) This part works fine.

Please Login or Register  to view this content.

Here is the jump tag section:

Please Login or Register  to view this content.

pretty much same thing, but I am trying to redefine the range within the inventory list to begin where I left off on page 1. This next line is the one that doesn't work.

Please Login or Register  to view this content.

Sure, it's convoluted, but if I knew a better way, I wouldn't be here.


Any help, including a complete rewrite using "do...while" or other construct would be gratefully received. Thanks in advance.

Conor

[royUK]

best to explain what you want to do, the code doesn't need to select & it's better to avoid loops if possible

[Conor]

Essentially, I am taking rows of info from one sheet and copying it to two others. I want to limit how many rows are copied to the first sheet, then start on the second, but not lose track of where I left off on the first.

I have an inventory list on sheet 1. It has 46 items. I want to read thru those 46 rows, and if the qty to ship is > zero in column 7, write the row detail to sheet 2. But only up to 23 items, even if I have to read more than 23 rows to locate them. Let's just say I read 30 rows.

Then I want to go to sheet 3 and write the remaining shipping items from between rows 31 and 46 of sheet 1 (still based on column 7 > zero), in this example. That may be 1 or up to 15; again the numbers relate to this specific example.

I enter the process knowing in advance how many of the 46 rows have shipping quantities. So, for instance, if that number is 28, I want to go thru as many rows as needed to fill sheet 2 with 23 items, even if the rows processed exceeds 23. Again, let's say 30. Then I want to start with row 31 of sheet 1 and write the remaining 5 rows with ship qtys to sheet 3.

Hope that is clear. Thanks.