Hello,
I'm a beginner in VBA so please bear with me.
I'll have a table that'll contain thousands of raw data.
Resolve time is the name of the column that I need to work with. I've assigned it to a named range.
This is how it looks like:
It's formatted as
dd/mm/yyyy hh:mm:ss
I'm in need of a specific operation that'll use an array and loop through the elements of that array. At least that's what I think I need.
What It needs to do: assign the whole column to an array, loop through every employee's record(Resolve Time) and "do sth" if one subtracted from another and the result is less than AHT of the first case for the same employee.
The real life application for this macro so you can have a clearer picture:
We do content moderation. The raw data source includes the cases they moderate and their resolve times. If an employee's record indicates overlapping values like the one I highlighted in the picture, it means they are moderating two cases at the same time which is what we don't want. So the next case they take should be at a time longer time than their AHT(Average Handle Time).
In this case:
Employee X finished moderating a case at 23:58:30 with 14 secs AHT and another one at 23:58:37. Second case, with 14 secs AHT of the first case should have been taken at 23:58:44 but it was taken at 23:58:37 which tells us that he was moderating two cases at the same time.
Now the macro needs loop through every record for every employee and "do sth" if such cases found.
The formula should be :
IF (Second Case Resolve Time) - (First Case Resolve Time) is less than AHT of the first Case
"Do sth"
The thing to note is that: since it'll contain records for other employees as well, the operation needs to work with every employee individually.
This is my incomplete macro:
Sub Identifier()
Dim Raw As Worksheet: Set Raw = Sheets(2)
Dim RTArr() As Variant
Raw.Activate
RT = Range("RT").Value
End Sub
I'm attaching a sample raw data source.
I hope I was clear enough. If not please tell me so I can try to explain better.
I'd really appreciate your help
Thanks.