Team, first time user here, I've spent days looking for a solution to this, but I think I'm close but I'm simply running out of time to turn this project in. I am looking for a VBA code that will allow me to auto-populate the first value on the first empty cell of column A & the last value on the first empty cell of column B, and then those two values will be subtracted from each other, to fill the first empty cell of Column C all while maintaining a 'h:mm' format (Hour: Minute) format on a Subtotal Grouping, and auto-populate this all the way down until the last row.
As an example, I've filled the end result, so notice that cell A6 = the first value of the group, B6= the last value of the group, and C6 is the subtraction of those two , in H:MM format.
<tbody>
</tbody>I am getting stuck on how to write the VBA code to autofill these 3 columns all the way down to the last row, while taking under consideration that these are being separated by empty rows in between each other to allow for 2nd header & the formula that allows me to get my desiered result.
I am currently using this VBA for column A but i know this is not right since i'm declaring specific reference cells and obviously the report will always have more or less rows,
Sub TEST()
With Sheets("OKLAHOMA")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A2:A200" & lr).SpecialCells(xlBlanks).Formula = "=INDEX(A2:A200,MATCH(TRUE,INDEX((A2:A200<>0),0),0))"
End With
End Sub
Any help would be greatly appreciated, I've been using this forum for about 1 month or so and it's been extremely helpful but as I've mentioned I've spend days trying to figure this out and i can't seem to find anything. Thanks in advance!!
As an example, I've filled the end result, so notice that cell A6 = the first value of the group, B6= the last value of the group, and C6 is the subtraction of those two , in H:MM format.
A | B | C |
Start Time | Arrival Time | Dep Time |
6:46 AM | 7:00 AM | 7:05 AM |
7:05 AM | 7:08 AM | 8:08 AM |
12:17 PM | 12:18 PM | 12:54 PM |
6:46 AM | 12:18 PM | 5:32 |
Driver Started | Driver Finished | Total Time |
7:27 AM | 7:39 AM | 7:31 AM |
3:07 PM | 3:17 PM | 3:37 PM |
Driver Started | Driver Finished | Total Time |
<tbody>
</tbody>
I am currently using this VBA for column A but i know this is not right since i'm declaring specific reference cells and obviously the report will always have more or less rows,
Sub TEST()
With Sheets("OKLAHOMA")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A2:A200" & lr).SpecialCells(xlBlanks).Formula = "=INDEX(A2:A200,MATCH(TRUE,INDEX((A2:A200<>0),0),0))"
End With
End Sub
Any help would be greatly appreciated, I've been using this forum for about 1 month or so and it's been extremely helpful but as I've mentioned I've spend days trying to figure this out and i can't seem to find anything. Thanks in advance!!