How to make a formula move down one to a new row each.

[BREECHEEZ87]

I have a "For" Loop that runs to help me in a scheduling program. Each 'For' until a specified Qty is reach copies & pastes a new line in a sequence of planning days, for example, planningday=1, then 2, then 3...until the set number is reached.

I added the code to assign a "Day 1" when planningday =1, and "Day 2" when planningday =2. I have two problems though, I can not get my formula to replicate in subsequent cells, i.e. each line that is copied & pasted in should be assigned a "Day #" and also as it is set up now, the day changes bc the planningday cycles 1 through set #., so when it starts, it reads correctly, Day 1, but by planning day 4, it reads Day 4, when the top line should say Day 1...then Day 2...then Day 3...etc.

This is the code:

Please Login or Register  to view this content.

The italicized lines are what I added, I think my logic is too simple for what I need it to do.

EDIT **I hope this works. I've had issues trying to get help with this.
Here is an overview of what is going on, the table in COL Schedule is updated in the first set of code, then a ranking is created in the second set of code. It is ranked by columns K divided by H, which creates a percent of Safety Stock, then that is sorted within the same code least amnt to highest. So that the Item number with the biggest gap between the % and 100% is ranked at the top.
Then, Based on what is ranked at the top, the program pulls a batch size (based on information put into the tab ColSched Start Here) and says we will build that batch size., it copies & pastes (values only) that line with the Batch size (also known as BuildQty) into Columns Q ~ W . Then the bacth size is added to the Oh Qty in Column K, then the entire list is resorted and the process is repeated, until the desired daily rate is reached (also in Tab ColSched Start Here). Then the entire process is repeated for the specified # of days, 1 to 5 or something. In the example given batch size 18, daily rate 54, days planned 4, so each time 3 lines would be pasted it would move to the next planned day.
If you watch Column X, listed as Day, you will see the value fills in based on what day is being planned, 1~4 or given #. what I need is an assigned "day 1", "Day 2", etc, to fill in each time a line is pasted, and the value must match the lines planned day not the current planned day.

I'm horrible at explaining things, I hope this isn't too big a mess.**

[BREECHEEZ87]

Also, when I tried to use the Paste formula right above my if statement, it kept selecting all the way tot he very bottom of the sheet, which is not what I needed it to do.

[FDibbins]

Hi, welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I have added them for you - this time )

[MatrixMan]

Can you attach the file so we can look at the whole thing and step through the code to see what it's doing?

In any event the code you italicized:

Please Login or Register  to view this content.

... can be replaced with:

Please Login or Register  to view this content.

[BREECHEEZ87]

I did this, but it didn't really fix anything for me... I did go back & edit my original post to include the worksheet

[BREECHEEZ87]

Thank you, I went back & read the rules too.

[MatrixMan]

I think this should do the trick ...

Please Login or Register  to view this content.

[BREECHEEZ87]

This worked like a dream, I really appreciate the help. I just wish I knew how/why it worked. I feel like an idiot, with so little knowledge of VBA/Macros.

[MatrixMan]

You're very welcome - don't feel bad about it - I don't know how to fly a plane and I don't think I'm an idiot ... I've just not been trained as a pilot. Don't forget to mark the thread solved and click Add Reputation :-)