VBA - create a Loop to distribute dates along a year as per Maintenance frequency

[Luthius]

Guys
I have the scenario below where I would like to create a loop to distribute along the columns (G column on) the date that that maintenance will ocurr based on its ID, Frequency and Due date. I will distribute it for a period of 01 year (365 days or 365 Columns)

For instance, the Maintenance for the ID 9960000192374 ocurrs every week and every month (Different maintenance , so my loop will fill an "x" the next due date to 08th Jan, and the next to 15th Jan, etc until reach the last week of a period of a year. After it, will go to the next row performing the next distribution.


The frequency varies, based on values of the frequency column.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Equipment[/TD]
[TD]Discipline[/TD]
[TD]Maintenance[/TD]
[TD]Frequency[/TD]
[TD]Due date[/TD]
[TD]01/Jan/18[/TD]
[TD]02/Jan/18[/TD]
[TD](...)[/TD]
[/TR]
[TR]
[TD]996000019374[/TD]
[TD]Equipment A[/TD]
[TD]Mechanical[/TD]
[TD]A[/TD]
[TD]1 Week[/TD]
[TD]01/01/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]996000019374[/TD]
[TD]Equipment A[/TD]
[TD]Instrumentation[/TD]
[TD]B[/TD]
[TD]1 Month[/TD]
[TD]01/01/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]996000019238[/TD]
[TD]Equipment C[/TD]
[TD]Electrical[/TD]
[TD]C[/TD]
[TD]3 Months[/TD]
[TD]01/01/2018[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











The original file contains more than 10k lines, the above table, is a short version of the problem.

Excel Columns reference based on above table:
ID= Column A
Due Date= Column F
(..) Means the others dates until 31st December

How can develop a loop that can distribute these dates?

 

[gmhumphr]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

Unless you are dead set on using VBA, you can do this through a formula, with one prerequisite: make a lookup table to convert the text of the units (week, month) into numbers of days.


Action:
1. In a new tab (can be hidden afterwards) called "Lookup", paste the following table in cell A1:

[TABLE="width: 166"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Day[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Months[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Quarter[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Quarters[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD="align: right"]365[/TD]
[/TR]
[TR]
[TD]Years[/TD]
[TD="align: right"]365[/TD]
[/TR]
</tbody>[/TABLE]


This lets you parse the text of the frequency from the numerical count, and convert it into a number base (which will then be multiplied by the numerical count to convert into days). Example, line 3 would eventually turn into 3 x 30 = 90 days, the frequency

2. Assuming the schedule table you pasted above has the first cell you need to populate for 1/2/18 in cell H2, paste the following function in and copy it across and down:
=IF(MOD(H$1-$F2,LEFT($E2,SEARCH(" ",$E2)-1)*VLOOKUP(TRIM(RIGHT($E2,LEN($E2)-SEARCH(" ",$E2))),Lookup!$A$1:$B$8,2,0))=0,"X","")

This parses the text (with the lookup), multiplies it by the units, and then compares it to the difference from the current date and the due date and sees if it is a clean multiple (through MOD) of the number of days between service.

NOTE: This is a little bit dumb because it assumes all months/quarters are 30/90 days (not varying 28/29/30/31...)

 

[Luthius]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

Thank you very much for your formula and feedbacks.
But, how can we do it with vba? I mean, the idea is not only use in a file but create an add-in because the data is "alive" and different departments handle with the data (different ones of course).

 

[Luthius]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

Anybody?

 

[MickG]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

With you data as per Thread and your Dates starting "G1 to NG1" then try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Apr13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dt1 [COLOR="Navy"]As[/COLOR] Date, Dt [COLOR="Navy"]As[/COLOR] Date, col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] w [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] V [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Offset(, 4).Value
        [COLOR="Navy"]Case[/COLOR] "1 Week": V = "ww": n = 52
        [COLOR="Navy"]Case[/COLOR] "1 Month": V = "m": n = 12
        [COLOR="Navy"]Case[/COLOR] "3 Months": V = "q": n = 4
    [COLOR="Navy"]End[/COLOR] Select
    Dt1 = DateAdd(V, -1, Dn.Offset(, 5))
[COLOR="Navy"]For[/COLOR] w = 0 To n - 1
    Dt = DateAdd(V, w, Dn.Offset(, 5))
    col = Application.Match(CDbl(DateValue(Dt)), Range("G1").Resize(, 366), 0)
    Dn.Offset(, 5 + col) = "x"
[COLOR="Navy"]Next[/COLOR] w
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Luthius]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

Thank you very much.
I would like to put one condition, I mean, if there is a blanked date in the column, the due date will be one week from the current date.

I tested with a few data and was ok.

 

[Luthius]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

I would like to increment with the following.
I need to group some equipment, I mean, I need that the due date of them will be the same, so the maintenance can be done in the same period.
In a different sheet I will have a list of these equipment, lets say, ListOfEquipmentToGroup (A2:A20).

The code will check the earlier due date from the list before (as the code before was formatted - F Column) using criteria the equipment list from the other spreedsheet, and based on the frequency distribute the dates as was developed on code before.
In case the equipment is not in the list, the code will continue with the distribution as was developed on code before.

The main idea is to distribute the dates along a period with additional criteria (List of specific equipment with the early date) as well. If is not part of the list, the code continues the distribution normally as before.

 

[MickG]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

Please provide an example of "ListOfEquipmentToGroup (A2:A20)" and corresponding results in Calendar.

Q(1) Does "Due Date" reflect the date in the Calendar when the "x's" should start, at the moment its the "1/1/18" ,if you changed that date to say "1/4/18" is there a chance that you would run out of calendar dates because the End date would then be in the next year, which might not be part of the dates in row(1).

 

[Luthius]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

On sheet ListOfEquipmentToGroup I will have some equipment that the maintenance needs to start together. Doesn't matter if the frequency can vary, but the first date, they must start together.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Equipment[/TD]
[TD]Start Date[/TD]
[/TR]
[TR]
[TD]Equipment A[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]Equipment C[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]Equipment D[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The dates I put is just one example. It can vary from 1st to nth date as per listed on my spreadsheet. Some starts on January 1st, some start on Feb 9th and so on.
The main thing is I will distribute these dates along a period of one year and I will have as criteria for grouping, a list of equipment on other spreadsheet called ListOfEquipmentToGroup.

 

[MickG]

Re: VBA - Help on create a Loop to distribute dates along a year as per Maintenance frequency

I'm sorry but you have not answered my question.

Please provide an example of "ListOfEquipmentToGroup (A2:A20)" and corresponding results in Calendar.

Q(1) Does "Due Date" reflect the date in the Calendar when the "x's" should start, at the moment its the "1/1/18" ,if you changed that date to say "1/4/18" is there a chance that you would run out of calendar dates because the End date would then be in the next year, which might not be part of the dates in row(1).

To Clarify:- Do you want the dates from ListOfEquipmentToGroup Places along the calendar in place of the previous "x's", and relating to "Equipment" name

also, If the start date is later than the "1/1/18" the existing code will overrun the end of the year and possible Crash if there are no dates for the next year in row (1.
Do you want the Input from the code to always stay within a Calendar dates of 1 year i.e. 1/1/2018 to 31/12/2018