Making a date range using start of week?

Hi Fawkes_,

Give this a shot:

Please Login or Register  to view this content.

If I understand you correctly, you want to highlight any cells in a column which fall into a two week window between the Monday before today and two weeks from then (for example, Monday 30th January to Sunday 12th February, if working on today Wednesday 2nd February).

You could do this with a Conditional Formatting rule. Select your range (I'm going to assume it's column A, starting in A2) then click 'Conditional Formatting' on the Home tab, then 'New Rule' then 'Use a formula to determine which cells to format'. Enter this into the formula box:

Formula:

Please Login or Register  to view this content.

Choose the highlight you want (fill colour or text colour, usually).

The formula works by checking if the date is both:
• greater than or equal to the previous Monday, worked out with TODAY()-WEEKDAY(TODAY(),3)
• less than or equal to the Sunday two weeks later, worked out with TODAY()-WEEKDAY(TODAY(),3)+13)

Hope that helps.

Well, you can use any column for last row, but since you are checking the date, column B last row should work because if it has no date in it then it doesn't need highlighting, but your code will of course work too.
However, in the book you provided as an example there is no date that is in the 2 week window of this past monday to 2 weeks later (Jan 30 to Feb 12), so no date to highlight?

Unless you particularly want to use VBA (which is a valid choice, of course) then you can amend the Conditional Formatting easily to highlight the whole row.
Amend the formula used to this:
=AND($B12>=(TODAY()-WEEKDAY(TODAY(),3)),$B12<=(TODAY()-WEEKDAY(TODAY(),3)+13))
then apply it to the whole table - go to 'Conditional Formatting' then 'Manage Rules', select this rule and enter this in the 'Applies to' box:
=$A$12:$F$17
(expand the range to include all the rows of your table - or just make it $F$100 or $F$1000 - a big enough number to cover any expansion expected - up to you).

As an aside, if you do prefer to use VBA, you should consider putting Arkadi's code into a Worksheet_Change event, so that it runs whenever the worksheet is updated. An addition to clear the highlighting from cells which no longer need it is probably worth it too.
Try putting this code (Arkadi's with some additions) into the Worksheet code - not a module (press F11 to open the VBE, right-click the 'Sheet1' sheet, select 'View Code' and paste this in).

Please Login or Register  to view this content.

I've set it so that any change in the range B12:B1000 will activate the code - just change that if you need a larger range. The code could probably be tweaked to have that range set dynamically, but I didn't want to play around with Arkadi's code to try to do that, in case I broke it .
Edit: I have amended the range, to have the whole row highlight, by changing the last ws.Range("B" & i) to ws.Range("A" & i & ":F" & i)
Edit2: Because of the first edit, I had to clear any existing highlighting on the whole range, so I've amended that too - the Cells2clear bit of the code.

Hope that helps.

Id be making this into an ADD-IN so I could share it, this simple project keeps getting more things added so.... SMH, simple favors never stay simple lol. I see the A to F change for the color fill, would I be able to use the selection.entirerow ... or wait... how about lastcol? I could do the last call and that would clear that up right?

Please Login or Register  to view this content.

ugh, how does one add LC to an ("I" & I)??

I am really a pain today sorry guys

also tried this for the lc and it didn't work

Please Login or Register  to view this content.

I was thinking since this will always be a single sheet wk would using activesheet be better? also, im getting push back requesting that I use the column names instead of letter, is that even possible?

I'm not sure if you are working with my concept or Aardigspook, for now I'll back off so that we don't get confused, he seems to be on top of it.

A few questions:

- What do you mean by "column names"? Do you mean you want to identify the "short Date" column by its header in row 1? (You can set the column as a variable by finding the header, you can't use the header text to DIRECTLY address the column).
- You said you want to highlight the whole row.... I assume you mean the part of the row that is part of the table with data and not the WHOLE row across the entire sheet?
- You asked about activesheet vs using the name... if the sheet name could change but it will indeed ALWAYS be a one-sheet workbook then yes.
-With regards to the last column .... do you mean you always want to use the last column to calculate the last row?

deleted duplicate

@Arkadi
You're far better with VBA than I am (I'm a VBA-tweaker, rather than VBA-writer, really), so I'll leave this to you now - though I'll be watching with interest so I can learn .

@Fawkes_
You posted this in the VBA forum, sensibly, so sorry if I caused any confusion with my Cond-Formatting suggestions. Good luck.

Hi again Fawkes_

It is all done except the LSDate item... I need to understand better.
LSDate is found by finding the column where Shortfall is 0 after a number higher than 0... but only checking columns with shortage in the name? And what happens if all shortfall columns are 0? (so in row 1, shortage is 0 on 24 april 2017, so LSDate would be 17-April-2017 correct?)
Also, what do we do with LSDate after we find it?

@Aardigspook, no need to apologize at all! Your suggestion was a very logical and good one! It is great when more ideas are presented, I just thought it was best if you were in control for now, but then Fawkes_ said he wanted to continue with my code so I started again. I really appreciate your help in solving his problem

For any line that has shown a shortage we are looking for the date in which the shortage will clear this will appear in any column with the "date- Shortage" in the header. if the lines are not blue or yellow we can ignore them due to not running a shortage. if the shortage does not clear on the current forcast I guess we would just bypass it and do an on error perhaps? default message of some sort.

The LSDate would be the header for the preceding short as you described above. that is 100% correct.

the date pulled would be placed into an inserted column for the information.

So I would need to put an insert column next to the 1st short date and label it comments.

from my understanding, currently the coworker is at lunch so I cant confirm past that part

Thank you for all of your help, as well as AArdigspook for the insight and assistance as well you are both extremely patient and helpful.

Fawkes_

What if there is a shortage, it goes away, and then later comes back again? (for example row 17 in the example)

This report will be run weekly so I believe they are focusing on the most recent.


I was not expecting so much help, thank you for all the effort you are putting into assisting me.

To clarify, if shortage goes away, then comes back, then goes away again, then you want the latest date where it went away... and I assume if shortage came back, then does not go away, then you don't want a date in the new column? Can you tell me what the header of the new column will be? (I assume you will be adding that column)
I also want to confirm... if there was a shortage, then went away, then came back again and did not go away, report no date?

Lets see if this does what you want?

I tried to post just the code but got an sql injection error because of something in the code that the server didn't like

Ignore that I downloaded it and renamed it into an xl form and it worked. the code works great if I manually add the clear date first, if there a way to make the clear date column insert to the right of the 1st short date automatically?


SMH... well I love it, he wants more now, I need to make a return statement with the quantity, and the date for white columns... "8k pcs due at _ week of" , yellow is critical and needs to be manually worked by them so what we have is fine, and blues need a similar statement.

you sir have done more than enough already and I can start working the rest, unless you just love to code lol


Thank you again so much for everything

Originally Posted by Arkadi

I tried to post just the code but got an sql injection error because of something in the code that the server didn't like

This might be because of a > or < followed by numbers or letters in the code (see this thread and this one).

If it's possible to add a space before/after the > or < then try that - the VBE will normally remove the space when it's copied & pasted.
If not, then you could try what's called the 'black colour trick':
Instead of < 1234 (with no spaces), break it up by making the font colour of one of the numbers black, like this:
<1[COLOR="#000000"]2[/COLOR]34

Hope that helps.

I'm trying, but I can't figure out what in the code is getting rejected by the server.... why can't you open the attachment? can I attach it as a text file?

Thanks Aardigspook... tried those suggestions without much luck, but I won't keep trying if Fawkes could download it.

@Fawkes_ here is the updated version with your request:

Here is the code FINALLY! I added spaces and colored black a few items, though I think it was the trim replace replace line... cheers to Aardigspook for the tip!

Please Login or Register  to view this content.

Fawkes, just for learning, the Trim function removes spaces, but there is a non-breaking space character that is not a normal space, that's why I used replace with Chr(160) to remove the special space that you have between the date and the word shortage in your headers

I added the Clear Date by code (first checks if it exists). had not read the rest properly... I shall have a look.

Reading it over, I am not sure what you mean exactly.... 8k pieces due the week of...,

the 8K is based on what? and "the week of" is?

is the 8k AND the week of... the amount of shortage with the same logic as the date we put in clear date? Also where does this output go?

Hey fawkes... Youd asked me to insert the clear data... It was in my previous post.... Though your way works too. If I understood right, you want the new info to be a comment in the clear data column? Just to undertand fully..... Using row 17 as an example since it has shortages more than 1 time... Can you give me the coment you would like in that row? I mean for the new requirement .... Xxx pieces due at... Etc

Fawkes... If there are new requests just give me a full explanation and exanple. It is now 8pm here and computer is being used by family to watch a movie but i will reply in the morning.
I have been working with vba since 2012 i think... Before that i used only formulas

About your example... I don't understand... for row 18 you said 2000 week of 2/13, and 2000 week of 2/20, but on the sheet the shortfall is 4000 feb 13, and 2000 feb 20.
Jan 30 and Feb 6 also have shortage of 4000 but no comment? And later weeks also have shortfall

good morning Akardi,


I hope you are well today.

I regards to the examples, comments would only come if there is a positive change. so when we the shortage decreases. so we start with a 4000 as the shortage if it stays at 4000 or increase we would not make a comment until we see a decrease or a clear.


so in this instances the 4000 is the start and then it drops by 2000 so we got 2000 parts in the week before the change, just like with finding our first zero. but in this instance we would return a comment and then keep searching for the first clear as well.


the other requests I have received were for the critical window, they would like a popup box for them to input the number of days, at the end they would like the data to sort by 1st short date, and there are two types of reports involved one will have two columns that the other will not, so could there be a search for that header? and if that header is found could it add a column and use a formula in that column?




Fawkes_

Please Login or Register  to view this content.

Hey Arkadi,

On the test WS for this it returns a the same response for all lines. after line one.

"3000 pcs due on the week of 17-Apr-2017
3000 pcs due on the week of 6-Feb-2017
12000 pcs due on the week of 6-Mar-2017"

is there a way for the response to have the data returns in the comments column have the same layout as the pipeline column? where each response is stacked? Also, notices the name for the header in the column for comments is not filling though the code for it is the same as the clear date add... I tried to make changes but ended up just adding an extra column.

it seems like the math is going a bit off somewhere, would it be better to try and just do the first short and first clear and then try to add the intermittent changes after?

I was weary of the intermitten change portion when they suggested it because it would need to only draw from any negative change in the line.

From looking at the output it seems to be piling the reponses from row to row ontop of each other. what do you think?

also is there an easy way to sort the sheet by deceding values in the 1st shrt date column at the end of the run?

as well as adding a message/input box to pup-up to input the value for the critical window? some people have 2 weeks and some have four weeks, so I figure is a when the macro runs initially a pop up shows that allows for the user to put in the number of days needed we could return that to the number in the date change?

I just edited my post.... please try it the way it is now and tell me if it is better? It was just to fix the comments process... will look at your other items.

You will need to re-add your inputbox code changes, but this is fixed for comment format, and it also resizes the comment column and sorts:

Please Login or Register  to view this content.

On Site inventory... is that the heading "Stock"?

My copy sheet doesn't have all the columns, so I can't check the code, the formula is a bit complicated to build but lets see if this works:

Please Login or Register  to view this content.

Oh.... I forgot... I think you mentioned something about the comments... if the shortage goes up before it goes down.... do you want the first shortage or the highest number before it goes to 0?

Hey Arkadi,


Sheet 1 is without the colmuns for the search for the second part we discussed, and Sheet two has the two columns required.

I hope this is enough data, Also the comment data on the last code was changed back to the due week of we had prior for looks, I meant to have the same comments as before but have them stacked. so they listed like the pipeline but looked like the comment we originally had sorry for the mistake on my part.

there is one more big thing with item class that ill need to get sorted out before I bring it up but everything else so far is amazing, for real I owe you something let me know you're awesome.

Fawkes_

Ok ignore my last code post... some errors in there.
So if shortage is 2000 then 4000 then 0 the comment would still say 2000 pcs due .... without noticing the 4000?

if the shotage starts at 4000 and then goes to 2000 a comment would be generated, saying "2000 pcs due week of date", if the shortage then rises to 6000 there will be no comment to show the rise, but if it goes from 2000 to 1000 there should be a comment added that says "1000 pcs due week of date", however if it goes to 6000 and then the next shortage is 0 the comment should say "6000 pcs due to week of date"

if they could populate like this:

"2000pcs due to week of date
6000pcs due to week of date"

Pretty much, if there is a drop in the shortage it should provide a comment showing the drop weither it be 1 less piece or the full amount, but if there is a rise it should not.

that would be awesome which is what two codes ago did I believe right?

I am sorry for all the headaches my friend.

Fawkes

Its getting a little confusing lol, but give this a try and let me know how it does:

Please Login or Register  to view this content.

Good morning Arkadi,

The code looks amazing, works great on the first test sheet but on the second with the in transit portion I get and error 91. Not sure why thank you again for all of this


This is the line on the debug, "Object Variable or with Block Variable not set"

its in the in transit section

Please Login or Register  to view this content.

Oops... Ok, I set the range sdcheck just before the if statement (above your yellow line).... but if it is nothing, then I insert it... so we need to add a 3rd line inside that if SDCheck is Nothing, to set it to the new column... change this:

Please Login or Register  to view this content.

to include a new line like this:

Please Login or Register  to view this content.

You mean it is not sorting at all? Seems to work here... can you send over your version?

It seems to be sorting by the clear date and not the short date, also I can paste your code in here, im getting blocked by the site for some reason

fawkes, there are a number of replace statements, to post my code I had to highlight each instance of the word replace, and change the text colour... I used black so you can't tell.


Using the book you gave me before, with the 2 sheets, and my last posted copy of the code, the sort is working.

But... yes the code is set to sort by the Clear Date.... you wanted the 1st short date column?

Change the sort line at the end to this (if you want to sort by first short date column):

Please Login or Register  to view this content.

Lots of good ways, my preferred is to load or unload a menu whenever the workbook is activated/deactivated, so I have a macro to make or delete the menu, and then in the workbook events part (Double-Click "ThisWorkbook" in the vba editor) I put the activate/deactivate event which just calls the code:

In normal module:

Please Login or Register  to view this content.

Then in the workbook events:

Please Login or Register  to view this content.

Yes this should add new one below the originals if I'm not mistaken.... they actually get loaded/unloaded when the workbook is activated, so you will only see this set of menus when "this book" is the open one. Most of our programs reset the bar each time the workbook closes, but I need to upgrade that because it means closing one workbook resets all the menu items. The method I posted above does not have this limitation. In fact in the workbook module I have the call for addmenu in the worksheet activate event just in case too

What is the code for the menu? and the macro does still have Set WS = ActiveSheet right?

And FRT_Modification_v1 is the code we have been working on?
I'm not sure why it would invoke that test sheet in any way...

Lol... thanks Fawkes_, for the kind words and for the rep.
If you are happy with the solution, please remember to mark the thread as solved?

Yes myShort is an array that contains strings, and yes, I didn't expect more than 10 changes in quantity so it can have 10 comment lines, each consisting of 2 elements, quantity and date (since we use the 2 parts to build each comment for a given line). So the 1 to 10 is not really columns, but decreases in quantity. If you expect more than 10 lines in the comments because the quantities will change more before reaching 0, then just increase the 10... it does no harm since the code is set to ignore the blank members of the array. I only did it that way because I was having problems using the redim command with a multi-dimensional array, usually I would just dynamically change the 1 to 10 to fit the circumstance.

To format the dates the way you want, in the section that builds the message, change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Lol, well congrats on figuring it out on your own, never mind that I posted first, it is good you are learning
Thanks for the offer, your kind words, rep, and the chance to be useful are thanks enough! (and marking the thread as solved when the issue is fully resolved of course).

Happy coding, and I'm sure we'll talk again at some point

Professional? Lol this is just a volunteer site, but thanks

Nice work Arkadi. I'm glad you took this one over - I would never have been able to do all of that!

Lol "aardig" of you to say Aardigspook.... I suspect you know more than you admit... It did turn out to be a marathon eh!?
It was fun to be honest.

Fawkes pm me if you have issues using some of the things i used and ill explain.
And dont apologize... I didnt complain