Hello,
I am struggling with changing the first row of a range from within a For loop. I tried thinking of other ways to do it, but I can't think of any.
I am working with SAP here and it will only let me enter data in a number of visible rows at a time, then once I scroll down, row numbering starts again from 0 and so on until I loop through my whole range.
The problem is I need to somehow change the range from within the For loop, or do it in another way I can't figure out.
In the sample file I would insert in SAP rows 5-8 as the first batch, scroll down, then insert in SAP rows 9-10, 12, 13, and so on until I insert all 27 rows in the filtered range.
The code in the attached file is working with SAP until I scroll down the first time and need to resize the range (move FirstRow down 4 positions and keep LastRow the same).
This doesn't need to be done the way I tried - but it's the only way I could think of doing it.
Please see attached file for a sample of raw data, my code in Module1 and a few explanations in Sheet1.
Also posted this request for help in here: https://www.computing.net/answers/of...oop/21856.html
Any suggestions?
Let me explain a little better, leaving out the SAP code.
- In Sheet1 I have a table starting from row 32 to row 6836
- That table is filtered to, let's say, 27 visible rows (my Rng is the 27 visible cells in column B)
- FirstRow (cell 1) of Rng is B33, LastRow (cell 27) of Rng is B6005
- I need to loop through Rng, 4 rows at a time (but not always 4, may as well be 8, 13 etc), and copy the data of each row to another sheet (let's call it Sheet2)
- On Sheet2, the 4 destination rows (but not always 4, may as well be 8, 13 etc) each have a number (RowNum) starting from 0 to 3 (or from 0 to 7, 12 etc)
- to clarify, RowNum is 4 for this Rng loop, won't change during the same "run" on Rng (it may be different from 4 on a different monitor or laptop, but on the same screen it remains the same)
- So data from cell 1 of Rng (FirstRow) goes in Sheet2 - RowNum 0, data from cell 2 of Rng goes to RowNum 1, cell 3 goes in RowNum 2 and cell 4 goes in RowNum 3 (RowNum is a must in my SAP formula as to insert data in there I need to address SAP row by number)
- After reaching cell 4 of Rng with data inserted in RowNum 3 on Sheet2, I need to change FirstRow from cell 1 of Rng to cell 5 of Rng
- Then I need to copy data from cells 5-8 of Rng to the same RowNum 0 to 3 of Sheet 2
- And so on until I do it for all 27 rows of Rng
So I have to do a loop of each of 4 cells (cell 1-4) of a 27 cells Rng and insert data from each 4 cells into cells 0 to 3 in another range.
The batch of cells is 4 only for this example and is the same for the entire Rng of 27 cells.
But it must be a variable. If I move the SAP window on another screen, the number of rows (4 in this case) is increased accordingly (to 13 for example).
Bookmarks