Calculating dates in reverse; always 'land on' a Friday

[dmcnab]

Hi Don.....amazing....so far, very good...I have made a couple of tiny changes to layout etc and adjusted code as req'd (always making a back-up as I go) but so far it looks very good.....I was toying with the idea of trying to count the number of times in a year that there is a postponement.........not how many times a particular person postpones, but the total number of postponements. I have tried some variations of 'counting' formula in a column on the far right (rather than insert a new column and have to adjust code etc) but anything I come up seems to depend on making an entry in column AF (the 'postponed to' column) and of course if the item gets postponed again, it simply counts the new date in place of the previous new date (hope this makes sense to you).....I was thinking of a formula that would count each time that a 'postponed to' date is inputted into col AF, but would remember that entry if the item is postponed again........that way, there would be a calculation of the total number of postponements during each quarter, for example.....can you tihnk of anything simple that will do this (and when I say simple, I mean something that is pretty much just a formula, rather than re-writing the Worksheet change codes or the Rescheduling code.....

[wdwells]

[quote name='dmcnab' post='785322' date='19-Jul-2009 18:36']Hi Don.....amazing....so far, very good...I have made a couple of tiny changes to layout etc and adjusted code as req'd (always making a back-up as I go) but so far it looks very good.....I was toying with the idea of trying to count the number of times in a year that there is a postponement.........not how many times a particular person postpones, but the total number of postponements. I have tried some variations of 'counting' formula in a column on the far right (rather than insert a new column and have to adjust code etc) but anything I come up seems to depend on making an entry in column AF (the 'postponed to' column) and of course if the item gets postponed again, it simply counts the new date in place of the previous new date (hope this makes sense to you).....I was thinking of a formula that would count each time that a 'postponed to' date is inputted into col AF, but would remember that entry if the item is postponed again........that way, there would be a calculation of the total number of postponements during each quarter, for example.....can you tihnk of anything simple that will do this (and when I say simple, I mean something that is pretty much just a formula, rather than re-writing the Worksheet change codes or the Rescheduling code.....[/quote]
Hi David
You musn't let the code frighten you. Two simple changes.

In the Worksheet_Change procedure of the Calendar sheet code change line 10
From: 10 If Target = EntryVal Then Exit Sub
To: 10 If Target = EntryVal Or Target.Row < 12 Then Exit Sub

Add the following line immediately before the GoTo ExitHandler command in the Postpone procedure of the Reschedule module;
Range("AF7") = Range("AF7") + 1

[wdwells]

David
You have deleted code which is required to ensure the macros are fired each time. It is necessary that you:

Add the following code to the ThisWorkbook code module

Private Sub Workbook_Activate()
 
	 If ActiveSheet.Name = "Calendar" Then _
		 Call Worksheets("Calendar").Worksheet_Activate
 End Sub
 
 Private Sub Workbook_Open()
	 Call Workbook_Activate
 End Sub

And add the following code to the Calendar Worksheet code module

Private Sub Worksheet_SelectionChange(ByVal target As Range)
	 EntryVal = ActiveCell
 End Sub

[wdwells]

To protect the user from selecting a holiday as the new date it will be necessary to add line 50 from the following code, to the postpone procedure.

Public Sub Postpone(target As Range)
	   Const MsgTitle = "Rescheduling"
	   Dim NewDateRow As Long
	   Dim Test As Variant
	   Dim i As Long
 
 10		Application.EnableEvents = False
		   
		   'Locate the first row of the requested new date
 20		On Error GoTo BadDate
 30		NewDateRow = Application.WorksheetFunction.Match(target, Range("F:F"), 0)
 40		On Error GoTo 0
		   
 50		If Range("G" & NewDateRow) = "H" Then GoTo BadDate
		   
		   'Locate the first available seat
 60		For i = 0 To 9

[dmcnab]

Hi Don....I have added the code that prevents people from postponing to a holiday, and that seems to be working well....and I added the code that counts the number of postponements.....it isn't workling so well....it is stuck at 2...I have not added the code to the ThisWorkbook code module and maybe that's the problem (ie: that the counting macro won't fire each time?).....to add this, I gather that I open the VBA portion of the workbook, click the + next to Microsoft Excel objects, clikc the "This Workbook" symbol and enter the code on the right...it will be two 'sections'..as in

Private Sub Workbook_Activate()
If ActiveSheet.Name="Calendar" Then
Call Worksheets("Calendar").Worksheet_Activate

End Sub

----------------------------------

Private Sub Workbook_Open()
Call Workbook_Activate
End Sub


..........and do I add the

Private Sub Worksheet_SelectionChange(ByVal target As Range)
EntryVal = ActiveCell
End Sub

as a new section of code in the Calendar worksheet code....just sort of insert it in between two other 'sections' or at the end of all other macros in that same module?

[wdwells]

[quote name='dmcnab' post='785490' date='20-Jul-2009 19:58']..........and do I add the

Private Sub Worksheet_SelectionChange(ByVal target As Range)
EntryVal = ActiveCell
End Sub

as a new section of code in the Calendar worksheet code....just sort of insert it in between two other 'sections' or at the end of all other macros in that same module?[/quote]
>>.....to add this, I gather that I open the VBA portion of the workbook, click the + next to Microsoft Excel objects, clikc the "This Workbook" symbol and enter the code on the right...it will be two 'sections'..as in . . .
Correct. Each of these sections is referred to as a procedure.

>>..........and do I add the
There is less likelihood of you placing it in the middle of another procedure if you place it at the end of the module.

I am amazed that postponement counter isn't working while the postponement itself apparently is. I would expect you to have trouble getting the postponement to work consistently until all of the code I recommended is in place. I am attaching a copy of my file complete with the code.

Keep us informed.

[dmcnab]

Hi Don....I actually think that I am starting to get the hang of this (and understand some of it).....I was able to get all of the code working and so far it is all looking pretty good. I did find one unexpected glitch; it did not surface until I got the 'rescheduling' component working properly. And, I had to make some changes...here is what I did (you'll see it in the attachment, which is what you sent me and what I modified):

(1) To print the Manifest, I would put my name or initials in I13; there has to be data in some of the cells in row 13 and if I postpone, say, the 1st item on Jan 2 to Jan 5, that row is empty and I cannot trigger a Manifest print by putting my initials in I13. I would then go to I14 and put in my initials, but if there is no data in AG14, it won't print...so-o-o-o, I now have data in all of col AG and I can trigger a Manifest print by entering data in any cell in col I.

(2) The Manifest is set up to show 4 (to a max of 6) items per day....once assigned to A("Lollipop") or B("Skipper" etc etc), the items don't move up the ladder, so to speak, if items above fall off.........if I postpone s/thing from Jan 2 to Jan 5, for example, I would trigger a Manifest print by entering my name in I14....this (as it should) displays what was formerly item #2 as item #1 on the Manifest.....it moves items below up the totem pole, as well.......however, it also moves what is item #1 on the Skipper manifest to item #6 on the Lollipop manifest.........whatever is booked to A or B, should always stay there.....you'lll see how I supplemented your code that prints the Manifest and it worked well when our sample only had 1 item....it works equally well if there are always 4 items per boat, but once that changes (eg: b/c of a postponement or cancellation) it gets out of sorts......I pretty much guessed at the code that fills in data for passengers 2,3,4,5,6 on "A" and for 1-6 on "B", but it needs to be adjusted in order to prevent the unwanted shifting from "A" to "B".

(3) you'll see that I added a col AE where I will track the 'original' reservation date....I gather that when the reschedule code runs, it won't carry formula with it? I had linked AG to the dates in F, but that didn't work b/c if something went from 1/2/09 to 1/6/09, it simply picked up 1/6/09 as the original (or postponed from) date.....then I figued that the postponed date could be manually entered if something is being postponed and I could adjust the Rescheduling code to also MOVEDATA in col AG.....if something is postponed, is there a non-manual way to have the 'postponed from' date follow in col AG to the new date?

Hope this is fairly clear.....#2 is really the only item that requires fixing....some of the approaches that I have taken or code I have used isn't that elegant, but everything (excpet #2) works fine.....thank you, again.

[wdwells]

[quote name='dmcnab' post='785675' date='21-Jul-2009 19:09']Hi Don....I actually think that I am starting to get the hang of this (and understand some of it).....I was able to get all of the code working and so far it is all looking pretty good. I did find one unexpected glitch; it did not surface until I got the 'rescheduling' component working properly. And, I had to make some changes...here is what I did (you'll see it in the attachment, which is what you sent me and what I modified):

(1) ...

(2) The Manifest is set up to show 4 (to a max of 6) items per day....once assigned to A("Lollipop") or B("Skipper" etc etc), the items don't move up the ladder, so to speak, if items above fall off.........if I postpone s/thing from Jan 2 to Jan 5, for example, I would trigger a Manifest print by entering my name in I14....this (as it should) displays what was formerly item #2 as item #1 on the Manifest.....it moves items below up the totem pole, as well.......however, it also moves what is item #1 on the Skipper manifest to item #6 on the Lollipop manifest.........whatever is booked to A or B, should always stay there.....you'lll see how I supplemented your code that prints the Manifest and it worked well when our sample only had 1 item....it works equally well if there are always 4 items per boat, but once that changes (eg: b/c of a postponement or cancellation) it gets out of sorts......I pretty much guessed at the code that fills in data for passengers 2,3,4,5,6 on "A" and for 1-6 on "B", but it needs to be adjusted in order to prevent the unwanted shifting from "A" to "B".[/quote]
Unless it is required that the first item in the Manifest be populated, adding line 785 between 780 and 790 as shown below should do the trick:

780		   If MTTest <> 31 Then Exit Sub
   785		   lRow = lRow - ((lRow - 1) Mod 12)
   790	   With Sheets("Daily List")

[dmcnab]

That does the trick exactly......thank you.....I was wondering, though, if another way to do it would be to use something like this:

Instead of MOVEDATA etc, if there is something like COPYDATA, I could use that in the 'Rescheduling' code (and include col AF).....the way I imagine it working would be that if col AE were linked to col F, and data was copied, say from row 13 (when something were postponed) instead of data in row 13 being moved, then I could enter a 'postponed to' date in AF and that would copy (and paste) data from row 13 into the new row (along with the info in AE, showing the original date).......then when the manifest prints, people would see that it was a full manifest, but that some items got postponed............and they would see the date that the item was "postponed to"....that would also ensure that if something was moved, that items below didn't somehow start to creep up into the manifest above..................and all of this depends on whether there is a COPYDATA procedure (instead of a MOVEDATA).....is this making sense? Is there such a procedure as COPYDATA or COPYPASTEDATA...??..??

[wdwells]

[quote name='dmcnab' post='785688' date='22-Jul-2009 00:14'][/quote]

Inserting line 55 into the code as shown below should achieve the desired results.

		 Public Sub Postpone(target As Range)
		 Const MsgTitle = "Rescheduling"
		 Dim NewDateRow As Long
		 Dim Test As Variant
		 Dim i As Long
		 
			 Application.EnableEvents = False
			 
			 'Locate the first row of the requested new date
			 On Error GoTo BadDate
			 NewDateRow = Application.WorksheetFunction.Match(target, Range("F:F"), 0)
			 On Error GoTo 0
			 
			 If Range("G" & NewDateRow) = "H" Then GoTo BadDate
			 
			 'Populate the "Rescheduled from" cell
		 55  Range("AE" & target.Row) = Range("F" & target.Row)
		 
			 'Locate the first available seat

[wdwells]

[quote name='dmcnab' post='785675' date='21-Jul-2009 19:09']Hope this is fairly clear.....#2 is really the only item that requires fixing....some of the approaches that I have taken or code I have used isn't that elegant, but everything (excpet #2) works fine.....thank you, again.[/quote]
In order to populate the "Prepared by" field of the report it is necessary to change line 3010
From: 3010 .Range("u5") = Range("i" & lRow)
To: 3010 .Range("u5") = target

[wdwells]

David

The attached file contains the changes recommended above. Additionally; In order to guarantee completing the header data in the report; I have cleaned up reworked and renumbered the Worksheet_Change procedure.

[dmcnab]

Hi Don.... ...I have made all the changes you suggested....when I did #53 (below) the result is that if there is an unpopulated row at the start (eg: row 13 or in the returned sample, row 13 & 14) then the deadline dates don't show up on the Manifest (seems that the 1st item in the Manifest for each day must be populated, or else the deadline dates won't find their way onto the manifest)......

(780 If MTTest <> 31 Then Exit Sub
785 lRow = lRow - ((lRow - 1) Mod 12)
790 With Sheets("Daily List")

I could do a work-around by changing the code in col Q,V,X,AB so that the date is always displayed even if there is no data in col K, but that would make the sheet look rather cluttered, don't you think (ie: all these rows that are empty expcet for the deadline dates).....and I suppose I could cond format the cells to have the text turn the same colour as the background and then it wouldn't be cluttered looking..??..??

[wdwells]

[quote name='dmcnab' post='785857' date='22-Jul-2009 20:56']Hi Don.... ...I have made all the changes you suggested....when I did #53 (below) the result is that if there is an unpopulated row at the start (eg: row 13 or in the returned sample, row 13 & 14) then the deadline dates don't show up on the Manifest (seems that the 1st item in the Manifest for each day must be populated, or else the deadline dates won't find their way onto the manifest)......

(780 If MTTest <> 31 Then Exit Sub
785 lRow = lRow - ((lRow - 1) Mod 12)
790 With Sheets("Daily List")

I could do a work-around by changing the code in col Q,V,X,AB so that the date is always displayed even if there is no data in col K, but that would make the sheet look rather cluttered, don't you think (ie: all these rows that are empty expcet for the deadline dates).....and I suppose I could cond format the cells to have the text turn the same colour as the background and then it wouldn't be cluttered looking..??..??[/quote]
I take it that you have downloaded the cure found at post 57. Let me know if you find further problems.

[dmcnab]

I think that our posts crossed in the mail....I saw it after I submitted my post and so I am looking at it right now....when you say that "In order to guarantee completing the header data in the report; I have cleaned up reworked and renumbered the Worksheet_Change procedure.", do I gather that you did this b/c you recognized the problem that I mentioned in my previous post of a few minutes ago?