Start Free Trial

asked on

Update Time value automatically

Hi Experts,

How to make the attached script update the time sequence at Column A if one of the cell at Column D data (total duration) is updated with a new duration. Currently any changes that were made on Column D only impacting the following cell at column A, other cells remain unchanged. E.g. If I modified data at cell D17, data at cell A18 (time) will be updated, however this time changed also impacting other cells at Column A but this not being reflected. Hope Experts could help to add this function.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count > 1 Then
    For Each cel In Target.Cells
        Call Worksheet_Change(cel)
    Next cel
    Exit Sub
End If
If Target.HasFormula = True Then Exit Sub
If Target.Column = 1 Then
    If Target.Row < 9 Then Exit Sub
    If Target.Offset(-1, 0) = "" And Target.Offset(-3, 0) = "" Then
        Target.Offset(-4, 3).Value = 1 + Target.Value - Target.Offset(-4).Value
    ElseIf Target.Offset(-1, 0) <> "" Then
        Target.Offset(-1, 3).Value = Target.Value - Target.Offset(-1).Value
    End If
ElseIf Target.Column = 4 Then
    If Target.Offset(1, -3).Value = "" Or Target.Value = "" Then
      Exit Sub
    ElseIf Target.Value + Target.Offset(, -3).Value > 1 Then
        Target.Offset(4, -3).Value = Target.Value + Target.Offset(, -3) - 1
    Else
        Target.Offset(1, -3).Value = Target.Value + Target.Offset(, -3)
    End If
End If
Application.EnableEvents = True
End Sub

Open in new window

Hi BillaZ

Is it essential you use VBA? this can be done with formulae as in attached. If this is OK, I am happy to tidy it up a little ...

Dave
fx-based-time.xls

ASKER

Hi Dave,

Initially I'd used the formula, but this is not workable and Experts has provided the VBA solution. All other functions works well in the attached script except the auto update. Hope you will consider. Here's the previous question link for your reference.

https://www.experts-exchange.com/questions/27496996/Add-Time.html

ASKER

Hi,

Another main reason that prevents me from using the formula solution is I need to remove the column D (total duration) after completing the whole process.

What is it supposed to do ?
if duration is changed in D8 then
A9 = A8+D8
and then
A10 = A9+D9 etc ... ?
is that how it supposed to work ? if yes why then not put the formula in A to do this any reason why not to ? I have no problem looking at the macro but need your reply on this.
Any way the way the macro is designed is very poor as recursive !! I would do it totally diffrent
gowflow

ASKER

Hi Gowflow,

The reason I can't use the formula is I need to remove Column D for other function, neither convert the formula into value, later I'm going to use the same template. Hope you can help me re-create this code.

ok fine pls answer my question what is it supposed to do ?? how does it work ?
A10 = A9+D9
A11 = A10+D10 etc ...

if yes then what happened in 1 day at midnite after 0:00 ?? and what happens if value in duration is nothing ???
gowflow

Billa7 it seems your spreading yourself thin as asking for the same routine 2 diffrent questions I spoted the #REF issue isn't it related to this same sub or I am mistaken ?
gowflow

ASKER

Hi Gowflow,

A10 = A9+D9
A11 = A10+D10 etc ...

this is how the time need to be counted.

After midnight, the next date time need to be updated according to "duration" at column D. e.g. if Cell A28, (22:30; 1-Dec) and Column D28 with duration 2:30, therefore cell at A32 is updated with 01:00 (2-Dec). If the Cell D28 duration not crossing the midnight (1:00), thus no changes happen at Cell A32.

Nothing happen if Column D is empty.

ASKER

Hi Gowflow,

I having dilemma with this macro and try to find an alternative, which is not giving me an absolute solution.

ok pls answer my last post about hte #REF !!! as will give you a solution here and maybe if same sub it will fix your ref problem as well !! but I am changing the whole sub and its logic !
gowflow

ASKER

Hi Gowflow,

We can close the #REF Q with your sub as a solution.

not too quick I was just asking a question !!!
I will need to work on your issue as it is not that obvious !!!

Question
if in the first date we change one of the durations to be 0:45 instead of 0:30 in line 14 then we have something like this:
7:00      0:45
7:45      0:15
8:00      1:00
9:00      4:00
13:00      3:00
16:00      4:00
20:00      1:45
21:45      3:00
0:45      0:30
1:15      1:00
2:15      0:15
2:30      0:15
2:45      0:15
3:00      2:00
5:00      2:30

You tell me what values need to stay in this date and what need to go on the new date and what will happen to remaining durations ???
gowflow

ASKER

Hi Gowflow,

After midnight the changes happen on the next date.

7:00 0:45
7:45 0:15
8:00 1:00
9:00 4:00
13:00 3:00
16:00 4:00
20:00 1:45
21:45 3:00

this will move to next date
0:45 0:30
1:15 1:00
2:15 0:15
2:30 0:15
2:45 0:15
3:00 2:00
5:00 2:30

I'm not very sure with your last question "remaining duration", if you referring to the subsequent time, the time also changed based on total duration at column D (= A10+D10 etc ...)

I see ... we will have fun in cut paste ... did you ever had this routine work ever ???
gowflow

ASKER

Hi Gowflow,

not really.

ok then I feel better then coz it is so confusing the present routine !!! :) just be sightly patient and you 'll hopefully be satissfied !
gowflow

ok one more question
In your example you have duration until line 53 so at this line when times are corrected the time in the next item which is in line 57 will be 7:45 but on this line there is no duration so the next line will be 7:45 and the following will be 7:45 etc ... till the end of the file which is in row 794 !!! like for all subsequent dates the time will be changed to 7:45 is this ok ????????

Pls adivse enablin continue further coz this is where we are right now
gowflow

ASKER

Hi Gowflow,

The start of the day is (each day) at 00:00 (midnight). The time only changed if we update the Duration at the column D, if the cell is empty, the macro should not update the time.

well confusing !!!
I go back to the previous post then, you said it should be like this:
After midnight the changes happen on the next date.

7:00 0:45
7:45 0:15
8:00 1:00
9:00 4:00
13:00 3:00
16:00 4:00
20:00 1:45
21:45 3:00

this will move to next date
0:45 0:30
1:15 1:00
2:15 0:15
2:30 0:15
2:45 0:15
3:00 2:00
5:00 2:30

but all this data was in the previous date !!! and the next date has also duration so if you update each duration they will do the same for the next date and so on ...

Do you mean that when you change a duration it should ONLY affect all the data withthin the same date ???

gowflow

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Gowflow,

Looks cool! can we update the time automatically for a new entry, E.g when I update total duration at D56, A57 was not updated with a time, is that any chance to update this cell with a new time?

ASKER

Hi Gowflow,

I hope my request not confusing you.

ASKER

Hi Gowflow,

Please let me know if you need more input from me, dose my responds irritated you?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Gowflow,

It works superbly, the only problem I have now is its show an error as "object define error at this line

"If WS.Cells(I, "D") <> "" Then"

when I copy data from Column A:C into different workbook. I have a separate routine for this. Is that a way to avoid this error at the stated line. Your previous code not giving me this type error when I copied these columns.

ASKER

Hi Gowflow,

It's OK, I will try to use workaround for this error issue.

ASKER

Thanks Gowflow the great solution.

tks for the grade much appreciated. Pls do not hesitate to ley me know if you need more help.
in the worksheet_change event everything after the Exit sub ie your original macro you can delete that ! if you want so the code looks clearer
gowflow