Macro Code for set time schedule

[yncip]

Hello everyone, I've been figuring out VB code to set out time schedule for each date.
Here is my raw table.

No	Qty	Date	Time
1	2	18/8/2014
2	212	19/8/2014
3	211	19/8/2014
4	294	19/8/2014
5	463	19/8/2014
6	253	19/8/2014
7	21	19/8/2014
8	250	19/8/2014
9	11	20/8/2014
10	88	20/8/2014
11	2	20/8/2014
12	945	20/8/2014
13	790	20/8/2014
14	121	20/8/2014

<tbody>
</tbody>

and what I expect the table will shown after running the macro code is like this.

No	Qty	Date	Time
1	2	18/8/2014	1:00
2	212	19/8/2014	1:00
3	211	19/8/2014	1:00
4	294	19/8/2014	2:00
5	463	19/8/2014	3:00
6	253	19/8/2014	4:00
7	21	19/8/2014	4:00
8	250	19/8/2014	5:00
9	11	20/8/2014	1:00
10	88	20/8/2014	1:00
11	2	20/8/2014	1:00
12	945	20/8/2014	2:00
13	790	20/8/2014	3:00
14	121	20/8/2014	4:00

<tbody>
</tbody>

so there is several several criteria to decide the time:
1. if the date is different then the time will start again from 1:00
2. if the quantity is more than 500 it will add an hour to fill the time.

i hope my explanation is clear and really looking forward for your help as I've been trying to find the code for days.

Here is the code i've been trying but it keeps on showing the wrong time.

Sub try()Dim i, j, k As Integer
Dim cum As Integer
rowz = Range("B6").End(xlDown).Row


Cells(7, 17).Value = ("1")


For i = 7 To rowz - 1
cum = Cells(i, 10)
k = 1
If Range("P" & i) <> Range("P" & i + 1) Then
Cells(i + 1, 17).Value = ("1")


    Else
    cum = cum + Cells(i + 1, 10)
    'k = k + 1
    j = 1


    
    Do While cum < 500
        Cells(i + j, 17) = k
        i = i + 1
        cum = cum + Cells(i + j, 10)
        j = j + 1


        Loop
k = k + 1


End If
Next i




End Sub

Thank you

 

[dermie_72]

I put 1 Hr (1:00) in G1 and 2 hrs (2:00) in G2,
then used a formula to get what you wanted instead of using a macro.
D1 will be 1:00 always, so that was manually entered.
D2 was

=IF(C2<>C1,$G$1,IF(B1>500,E1+$G$2,E1+$G$1))

then i copied down.

If you need this by macro, you can populate the range

range("E3:E" & rowz).formula = "=IF(C2<>C1,$G$1,IF(B1>500,E1+$G$2,E1+$G$1))"

hope that helps.

 

[WarPigl3t]

I would like to point out first that your dataSet doesn't make sense. You said that the time should increment by 1 hour if the quantity was over 500. That was one of your criteria. Well look at your row 4 for example. That row quantity is under 500 and yet your output in the Time column still incremented an hour. Guess you messed up there.

So now for my solution. I prefer to use Functions rather than Subroutines. I find them easier to code because I don't have to keep writing "Range.("A1").value" every 2 seconds. Start by entering this code into your VBA macros.

Function myFunction(myQty, myDate, myPrevDate, myPrevTime, myChngeVal)
    If myQty > myChngeVal _
    And myDate = myPrevDate Then
        leftTime = Split(myPrevTime, ":")
        myFunction = leftTime(0) + 1 & ":00"
    Else
        myFunction = myPrevTime
    End If
End Function

Now enter this formula into your second Time cell. I'm assuming it's cell D3. Don't use absolute references. Just copy paste exactly what I put below...

=myFunction(B2,C2,C1,D1,500)

Now just copy the formula down the column. Easy.

 

[WarPigl3t]

I put 1 Hr (1:00) in G1 and 2 hrs (2:00) in G2,
then used a formula to get what you wanted instead of using a macro.
D1 will be 1:00 always, so that was manually entered.
D2 was

=IF(C2<>C1,$G$1,IF(B1>500,E1+$G$2,E1+$G$1))

then i copied down.

If you need this by macro, you can populate the range

range("E3:E" & rowz).formula = "=IF(C2<>C1,$G$1,IF(B1>500,E1+$G$2,E1+$G$1))"

hope that helps.

Why didn't I think of that. Duh!

 

[yncip]

Thanks for your answer.

I think I didn't explain it clearly, What i mean with point number 2, 500 is for the cumulative quantity not the quantity for each row.

That's what happened with point number 2 and 3 ; 6 and 7

No	Qty	Date	Time
1	2	18/8/2014	1:00
2	212	19/8/2014	1:00
3	211	19/8/2014	1:00
4	294	19/8/2014	2:00
5	463	19/8/2014	3:00
6	253	19/8/2014	4:00
7	21	19/8/2014	4:00

<tbody>
</tbody>

If we sum up the quantity for no.2 and 3 we will get 423 which is still below 500, that's why both of them still scheduled at 1:00.
So I think, we need 1 variable memory to record the cumulative quantity and compare it with the limit ("500"). But I still get stumbled with the looping and the coding.

Is there any other suggestions?

 

[dermie_72]

Cell D1 = 1:00
Cell D2 = =IF(C2<>C1,"1:00",IF(SUMIFS($B1:B$2,$C1:C$2,C1,$D1:D$2,D1)+B2<500,D1,D1+0.04166667))

That should provide you with the information you need.

Again, the same rule applies with the macro, for this formula.

Let me know if this works.