Hi all,
I'm using the following code to find the first and last value of "286" in column A - the output in V1 is the combined times of the first "286" and the relevant time in K and the last "286" and the relevant time in L.
I'm trying to use a scripting dictionary to replace "286" with all the unique values that are found in Column A, loop through them, and dump the resulting values in a list starting with V1.
I got this far...
But I'm unsure how to combine the what I've already got and the necessary For Each A1
Any help would be greatly appreciated!
I'm using the following code to find the first and last value of "286" in column A - the output in V1 is the combined times of the first "286" and the relevant time in K and the last "286" and the relevant time in L.
VBA Code:
Sub StartEndTimes()
Dim StartRow As Integer, EndRow As Integer
Dim StartTime As String, EndTime As String, StartEndTime As String
With Sheets(7)
StartRow = .Range("A:A").Find(what:="286", after:=Range("A1")).Row
EndRow = .Range("A:A").Find(what:="286", after:=Range("A1"), searchdirection:=xlPrevious).Row
End With
StartTime = Range("K" & StartRow)
EndTime = Range("L" & EndRow)
StartEndTime = Format(StartTime, "h:mm") & "-" & Format(EndTime, "h:mm")
Range("V1") = StartEndTime
End Sub
I'm trying to use a scripting dictionary to replace "286" with all the unique values that are found in Column A, loop through them, and dump the resulting values in a list starting with V1.
I got this far...
VBA Code:
Sub StartEndTimes()
Dim StartRow As Integer, EndRow As Integer
Dim StartTime As String, EndTime As String, StartEndTime As String
Dim Dic As Object
Dim A1 As Range
Set Dic = CreateObject("scripting.dictionary")
With Sheets(7)
StartRow = .Range("A:A").Find(what:="286", after:=Range("A1")).Row
EndRow = .Range("A:A").Find(what:="286", after:=Range("A1"), searchdirection:=xlPrevious).Row
End With
StartTime = Range("K" & StartRow)
EndTime = Range("L" & EndRow)
StartEndTime = Format(StartTime, "h:mm") & "-" & Format(EndTime, "h:mm")
Range("V1") = StartEndTime
End Sub
But I'm unsure how to combine the what I've already got and the necessary For Each A1
Any help would be greatly appreciated!