Having Trouble with a Loop

[Sozu21]

Hello all,

First post here. Looks like a great community!

I'm pretty new to coding macros and I'm having trouble figuring out how to code a loop that works for my specific scenario, described below:

I've been tasked to create a macro that checks for gaps in production time by employees. The meat of the code allows the user to specify a time range that they are interested in looking at, as well as a minimum amount of inactive time to be considered a time gap. It then formats the sheet as follows:


Column A - times of gaps (two rows per gap - start of time gap, followed by end of time gap)
Column B - username associated with gap
Column C - total duration of time gap
Column D - movement type (supplied from the data being parsed, allows user to know which activities were being done during the time gap)

The issue I'm having is when it comes down to calculating the durations of the time gaps in column C. Each non-consecutive time gap should consist of 2 rows, but if a user has consecutive time gaps (ie. the stop time of the first time gap is also the start time of the next time gap), the data is no longer an even amount of cells and the formula is not copied correctly.

How I originally had my loop set up was to check the username column (B) against the value of the cell above it. If the values were the same (ie. same user), calculate the difference of time in column C. However, like I said, if there was an odd amount of cells per user due to consecutive time gaps, it skewed the way the formula would be placed.

Here is an example of what typical output may look like when this issue occurs:

Conf.T        User       Gap             Mvmt
10:00:00     user1     00:20:33          987
10:20:33     user1                       987
11:34:09     user1     00:10:00          987
11:44:09     user1                       987
10:11:05     user2     00:05:43          983
10:16:48     user2                       983 (problem occurs on this row due to having consecutive gaps 10:11-10:16, and 10:16-11:01)
11:01:00     user2                       983

This is what the loop code is:

Lrow = Range("A" & Rows.Count).End(xlUp).Row
i = Lrow
Do Until i = 1
   If Cells(i, 2).value = Cells(i, 2).Offset(-1, 0).value Then
      Cells(i, 3).Offset(-1, 0).FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
    Else
    End If
    i = i - 1
Loop

While this works if every user has an even amount of cells, once a user has an odd amount, the formula placements go out of whack. Can somebody help me tailor the loop to account for the above scenario?

Thanks in advance for reading and any suggestions!

 

[Sozu21]

Is it possible for someone to help me out with this? I'd be very grateful!

Again, the outcome I am trying to produce is:

If a username in col. B matches the cell above it (or below it, depending on whether the loop travels down or up), then calculate the difference of time of the corresponding cells in col. A and put the total in col. C.

Example:

10:00:00     user1     00:05:00     987
10:05:00     user1                  987
11:00:00     user1     00:06:00     987
11:06:00     user1                  987
10:20:00     user2     00:05:00     983
10:25:00     user2                  983

The issue is if there are two consecutive time gaps (if stop time of gap A is start time of gap B), which would look like this:

10:05:00     user1 - start time of gap A
10:10:00     user1 - stop time of gap A/start time of gap B
10:20:00     user1 - stop time of gap B

Thanks again for any help/suggestions!