Looking for a way that can Leave dates out of range if not used

I have a range of dates that is compiled from dates in several rows on another sheet.

The entire date range has 28 days with in that range but there were 5 days with in that range that actually nothing happened.

So 22 days should be in the range but the range for the dates still displays 9/01 - 9/27 the entire 28 day range from your list.

Is it possible to have it group the date range only for the days actually used and show possibly 9/01 - 9/14, 9/21 - 9/27

My Dates look like this: These dates are in separate cells you can see a gap between days not selected
9/1 9/2 9/3 9/4 9/5 9/6 9/7 9/8 9/9 9/10 9/11 9/12 9/13 9/14 9/20 9/21 9/22 9/23 9/24 9/25 9/26 9/27

I also group them in one cell for my original solution it looks like this:
9/01 9/02 9/03 9/04 9/05 9/06 9/07 9/08 9/09 9/10 9/11 9/12 9/13 9/14 9/20 9/21 9/22 9/23 9/24 9/25 9/26 9/27

My original solution returns the entire range 9/1 - 9/27 I would like 9/1 - 9/14, 9/20 - 9/27

Can this be done?
I can attached worksheet if needed.

Here is my worksheet: You can see my original range output on Job ticket starting in cell (I10), It gets it date range from Daily Chgs Sheet (E3:BN50)

I duplicated that cell range way out to the left in (EI3:GR50) and it shows the date in the respective cell if it is checked then I add all the dates up in (GT3:GT50) - and that range gets entered onto the Job ticket Sheet. Let me know if I have explained it good enough

I tried the link it seamed OK is there any other way to get you the file...your my only response right now.

Either is ok I'm on my way home I'll try it out ASAP and let you know

This is Great How do I get the date to show complete right now it is (09/-09/, 09/-09/)

Also, If I need to add or delete some rows or columns can you give me a brief explanation of how the VBA works so I know what numbers to adjust.

Also Is there a way to make this macro to auto run in background so they don't have to choose the macro for it to populate.

You don't know how long I have been working on this and no one could help on this beside you...your a great help.

This is Great How do I get the date to show complete right now it is (09/-09/, 09/-09/)

Also, If I need to add or delete some rows or columns can you give me a brief explanation of how the VBA works so I know what numbers to adjust.

Also Is there a way to make this macro to auto run in background so they don't have to choose the macro for it to populate.

You don't know how long I have been working on this and no one could help on this beside you...your a great help.

I appreciate all your help I know It is a lot to get correct. Depending on how long Accounting will want the Job sheet when it prints out I may need to add or take away some rows on the Daily Chgs so How would I modify the range to work correctly if I make some changes (We have a specific sheet I will need to match for accounting when they actually print it out)

Also I am only getting the Month no Day is coming through (09/-09/) - Should be (09/01 - 09/08) as an example But I may need to have the Year also It will depend on Legal once other make the decision on what they want.

I think I have the macro run situation OK I just entered the macro in the activate section of the Job ticket so when you click on the Job ticket the macro updates. I think that is a correct way I might be wrong.

I have attached it again it is the same Sheet I sent ...all I did was add the code, but you can see the day is not coming through. How would I modify if we want to show the year.

I changed this line and it works pretty good. [Changed (-5) to (-3)] Now I'm getting the day.

DS = DS & Left(CStr(SD), Len(CStr(SD)) - 3) & "-" & Left(CStr(ED), Len(CStr(ED)) - 3) & ", "

I noticed if you skip one day it will still give you the whole range it does not break out that particular day.

EX: 9/1 9/3 9/5

It returns: 9/1- 9/5

Should be 9/1 , 9/3-9/5

Also when only one date is checked it returns 9/1-9/2 like it had 2 days checked can that be changed to read:

9/1 or 9/1-9/1.

I am juts trying to cover all the bases before I use.

Again Thanks for your help.

I tried adding that code but it made no difference let me know if you get it to work I am going to try and finish this to use at the office tomorrow.

Thanks Brian

Your awesome that is perfect thank you very much.

Brian

As I am going through adding Information making sure there are no bugs I have ran into a couple issues.

Sometimes when I make a new addition I receive a

Runtime Error : Application-defined or object-defined error - On line

If WF.CountA(wd.Range("B" & i).Resize(lr - i)) = 0 Then

Also ON my last Line when I choose a drop down on the Daily Chgs Tab - Row (B49)
it brings through the choice onto the Job ticket but I get no Dates Coming through.

Also sometimes when I go back and forth between the worksheet every now and then it will not run the Macro automatically when the Job ticket is chosen.
Any Idea why - Or should I add button to run Macro Instead.

I am trying to understand how this actually works but I am not very knowledgeable in writing code and all I know Is what I learn by trial and error.
Could you let me know why this might happen.

I attached what I have right now.

Also I have found that if after you have run the macro and you have your dates coming through if you go back to the Daily CHGS tab and remove your info from column B and any days that were checked when you rerun the Macro the dates still show in your dates serviced column you have to delete them by hand to get them to not show.

Also How do you change the format to only show the Month and day and the dates that are returned with only one date shown -

Could I get a breakdown on how this work so I would know what to change if I make additions going forward.

I went in an changed the range to how you had it on the previous sheet and it seems to not be generating the runtime error: Now with that being said I am not sure why the runtime error was happening in the first place.

Also I would still like your help on how to understand the code if I need to make changes and why the dates remain after you run the macro but have removed that data from the Daily chgs sheet it still keeps the data present. Possibly have it clear out those cells first and always rerun from scratch is my thoughts but your the expert.

Please Login or Register  to view this content.

That corrected the runtime error but here is a new error : Runtime error 5 invalid procedure call or argument with

DS = Left(DS, Len(DS) - 1)

If they go and delete the days relative to an entry on daily chgs tab but do not delete entire line of info including drop down list choice you get that error.
Is there a way to correct that.



Again thank you

Also, I had to delete 4 rows off of the Job ticket Tab so it would fit with in the correct paper we use. How do I change it to reflect that new range now.

I deleted rows 53 -56 on job ticket & 46-49 on Daily chgs so it should be good but the dates are not coming into last line on Job Ticket.

Strangely I have row 36 & 45 populated on daily chgs and when it populates the dates on job ticket everything comes in but the dates in row 52 on Job Ticket which is from line 45 on daily chgs.

but if I add another entry into line 44 on Daily chgs it will include the dates on line 51 and also will include the dates for line 52 then when the dates are populated.

I know this is probably annoying you but Once I get all the bugs worked out it will be done

You have the touch thanks I am going to put it through the ringer tomorrow hopefully no more issues.


Do you have any suggestions on material that would teach me this kind of code so I can learn and not have to bother so many people with easy fixes....I would like to start learning this on my own.

Any suggestions would be great.

Brian

I have been testing it and right when I thought it was ok I found a glitch.

I have attached so you can see it.
Look at Job Ticket (I10) Notice Range (09/02-09/14, 09/20-09/30,)

It should Be (09/02-09/14, 09/20-09/30, 10/11-11/01,)

Look at Daily Chrgs and goto Row 3 Column BN - You will see it is checked if you uncheck it then your will get range
(09/02-09/14, 09/20-09/30, 10/11-10/31,)

But if you have the last day checked the range does not come through properly.




Also Point #2- Can you get the date format to not show the comma, We like the Month & day format but when I adjusted to remove the year it brigns in a comma now.

No biggie but if it can be removed that would be great

Again you da man.

I found One more Glitch... On my sheet If I dont have a date in Cell (a8) on my Job Ticket when you click around ....( sometimes you dont have everything filled out yet)

But if you dont have a date in that cell (A8) on job ticket it will return :
runtime error 5 - invalid procedure call or arugment

on line--- DS = DS & Left(CStr(ED), EDLen) & ", "

Is there a way to get the macro to instruct the user to input the date in that cell on the job ticket so there is no debug issue or get it to exit the macro and disregard the error.

I am not sure if I explained it good so I attached the sheet also.

If the error does not pop up automatically hit the Populate dates if not automatic

Again Thanks

What does this one change

Also if they hit cancel on the pop up for the date it returns an error 13 type mismatch.

Can it just actually cancel if they hit cancel not the error code.

No you still get error code type mismatch

yes unless i did something wrong

Again Everything has been working great thanks to your help and Low and Behold The powers to be wanted to make another change -

Now we have a subset under Column "b" in the Daily Chrgs tab that filters your drop down into categories and I will have extra rowa that will not be populated. So what they asked to have done is that when my Job ticket gets populated into Column "a" for the service provided it removes the blank rows and compresses everything together but now that the VBA you wrote uses the Range we previously had to populate the dates it does not adjust for that.

Is there a way to have it adjust the range based on the Blank rows that get omitted that way the dates serviced is correct when that gets populated.

I have attached the way they are trying to modify the sheet so you can see what it is doing.

Please let me know if it can be done.

I was thinking that if we could Populate the date range for each individual line and have that return the answer in column D on the Daily Chgs... I could then carry it over the same way I am for the operations.

Column D is going to be hidden and not seen by the user. So if I could get that working I might be able and make it report on the corresponding row on the Job Ticket tab so my charges match up.

You might have a better way to do it I am just thinking how it might get accomplished.

Let me know thanks.

It looks to be working I do have a question about this line

Please Login or Register  to view this content.

In B2 on the Daily charge this will not affect what ever choice you have chosen there correct.

i was just trying to decipher what the MWD in the code meant.

I am sure your are aware that your drop downs are dependent on your choice in B2 ( Directional, MWD, WellGuide_RSS, Gyro_Guide_GWD, Wireline & Gyro)

It looks to be working though I am just trying to figure out exactly how it is working.

Again I wish I had your knowledge I will run some test this evening trying it out.....And there are a few other things they want to do i hope it does not effect the outcome.