CODEWITHSUNDEEP
vbaexcelexcel-2016
Iam_hafeez
Iam_hafeez

Reputation: 123

VBA - to Loop rows and insert value based on condition

My Question is about excel VBA looping. I want the loop to start populating the results from beginning when certain cell values are changed.

I wrote a macro to loop rows and insert value based on condition, however I am experiencing difficulty while including an additional criteria to begin the loop again when the cell value changes.

Condition No. 1 = Should start calculating the time window when the order date time is above 21:00 hours, however i want to loop till new date comes and start populating the window time again from beginning when the next date has 21:00

Considering 1 assignee can work on 10 orders in 1 hour, whereas 2 Assignee can work on 20 orders in 1 hour and so on....

Sub EstimatedTimeWindow()
Dim i as long, n as long,
Dim x as double
Dim p as long

    No_of_Orders = 20
    No_of_Assignee  = 2
    OrdersinHour = No_of_Orders  * No_of_Assignee

   Worksheets("final_data").select
   n = cells(rows.count,"A").end(xlup).row
   p = 0

   For i = 2 to n
   x = (1* cells(i,"A")/1)) - Int(Cells(i,"A")) 'to check the time
  If x >= 0.875 'time as 21:00
     p = p + 1
       If p <= OrdersinHour Then
          Cells(i,"B") = "Estimated window time 21:00 - 22:00"
       End If
       If p > OrdersinHour AND p <= OrdersinHour * 2 Then
          Cells(i,"B") = "Estimated window time 22:00 - 23:00"
       End if
       If p > OrdersinHour AND p <= OrdersinHour * 3 Then
          Cells(i,"B") = "Estimated window time 23:00 - 00:00"
       End if
       If p > OrdersinHour AND p <= OrdersinHour * 4 Then
          Cells(i,"B") = "Estimated window time 00:00 - 01:00"
       End if
  End if
Next
End Sub

sample_image

Upvotes: 1

Views: 1835

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

Misread the question: I have left that answer below, but it can be ignored. Updated answer added to the start:

Do not drop the Date from x - then, when the day in the cell is not the same as the previous row's day, reset p to 0

For i = 2 to n
    If Int(I) <> Int(cells(I,1).Value Then 'Order Date has changed day
        p = 0 'Reset counter p to 0
    End If
    x = cells(i,1).Value 'to check the time
    If (x mod 1) >= 0.875 'time as 21:00
'Continue as normal

You can call a Macro at a specific time with Application.OnTime - however, if you close the Workbook but leave Excel open it will reopen the Workbook to run the macro unless you disable the OnTime. It gets... messy.

A better solution would be to use the Worksheet_Change event to run code when the values on the Worksheet change:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns(1)) Is Nothing Then 'A Cell in Column A changed
        Call EstimatedTimeWindow
    End If
End Sub

Also, Quick tweaks to your existing code: x = Cells(i, 1).Value Mod 1 will give you the time with cleaner code, the Hour statement is easier to read than 0.875 and a Switch statement on p will make your code cleaner too:

For i = 2 to n
    If Int(x) <> Int(Cells(i,1).Value) Then
        p = 0
    End If
    x = Cells(i, 1).Value 'Since we take the Hour, no need to trim the date off first
    If Hour(x) >= 9 Then
        p = p + 1
        Select Case (p\OrdersinHour) 'Same as Int(p/OrdersinHour)
            Case 0:
                Cells(i,2).value = "Estimated window time 21:00 - 22:00"
            Case 1:
                Cells(i,2).value = "Estimated window time 22:00 - 23:00"
            Case 2:
                Cells(i,2).value = "Estimated window time 23:00 - 20:00"
            Case 3:
                Cells(i,2).value = "Estimated window time 00:00 - 01:00"
        End Select
    End IF
Next

Upvotes: 1

Related Questions