Hello everyone, I've been figuring out VB code to set out time schedule for each date.
Here is my raw table.
<tbody>
</tbody>
and what I expect the table will shown after running the macro code is like this.
<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.
Thank you
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.
Code:
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
Last edited: