Should be a simple fix but not sure how to move and tweak my If statement....

[breecheez87]

I'm new to learning this VBA stuff & I have taken over a project. This is where I am stuck.
I composed and if statement in TAB "COL Schedule" Column x right now is where I was successfully able to get my IF statement to go in & result in Column X "Day 1" if my planning day was Day 1.

Problem is when I added an Else, to try to put in planningday=2, 3,4,5,6...etc, it gave me a compile error. So I erased it. So issue one is how do I get the If statement to move down with each new copy & paste (written in the code) to Columns Q~ W (the data is resorted (Columns G ~ M)each run in my "for" statement and then the top ranking item is copied and pasted into column Q).
Second issue is how to complete my Else and match the "Day 1,2,3" to the planning days... I can probably figure this out later if I can get one successful "Day 1" assigned to each planningday = 1 line.18383

[Paul_Hossler]

Little hard to follow

Are you talking VBA macro or worksheet formulas?

You attached a XLSM file, but there are not macros in it. You said 'Compile Error' which probably implies macros

The only 'Day1' I see is in X2, but I don't see what it relates to

What are you trying to do and what is the logic to do it?

[breecheez87]

I'm not sure why it is you cant see my Macros, I did try to detached the sheet from the full document, so that may be the cause.
Here is what I can do. I copied the Macros over to the doc & will attach again. If you open VBA it's the only mod. attached to this sheet now.

Issue:

When I run my macros, I can get the basic If statement to work, based on the results in X2. It is tied directly to the line of code that is under 'Creates Ranking & is the second "For" statement, "For planningday =1 to Plandays"

What this does is sorts and re-ranks the columns G through L & tells the program based on what is farthest from 100% of the stocking target what to build next (the parameters are based on info I enter, batches of 10, and plan 2 days worth, up to 100 daily rate) so for example, it sorts and adds batches of 10 until it plans 100/day for 2 days.

It copies this information (to make a build plan about) into columns Q ~ W. I have an IF statement in X2 that returns a value based on that planning day the last line is, for example, Day 1 or Day 2. But It only changes the value in X2, what I need is the for the IF statement & subsequent value to fill in for each line until the daily rate (for how ever many days I chose) is fulfilled.

Does that make sense. I just don't know how to make the formula move down after each line is pasted in before it starts the For loop over.

One thing my boss suggested is to change it to a weekday formula, for example, If I plan on Monday 2 days out then Day 1 should read Tuesday and Day 2 would read Wed (based on what day I started) but I run into the same issue... I do not know how to make my formula move down each line, without manually writing a bunch of code for each line. also I do not know how to get a weekday formula work without a serial/# to reference.

I hope this makes more sense. TIA

[Paul_Hossler]

Sheet with apparently required parameters is missing so I can't test



Sheets("ColSched Start Here").Select
Batchsize = Range("D3")
Dailyrate = Range("D5")
Plandays = Range("D7"


Just tell me what the values are and I'll force them in for now

[breecheez87]

Sorry it wont work without the Start here tab. I added it & ran through the macros, looks like it should work now.

One thing I did notice, My forumula for column X did work (I did not fix the move down with each line problem) but I noticed another flaw, it updates X2 with the current "planningday" (so it will cycle through days 1-4, as its set up now) but I need it to assgin a Day 1,2... for each line, that does not change. I thought I could bypass this by adding a copy & paste value, but I still dont know how to move it down aside from manually typing each cell value.