Sub RunMe() Dim x As Integer Range("A1").Value = "12:32:44 AM" Range("A2").Value = "12:32:45 AM" x = 128 Range("A1:A2").AutoFill Destination:=Range("A1:A241"), Type:=xlFillDefault Range("A1:A241").Copy Do Range("A1").End(xlDown).Offset(1, 0).PasteSpecial x = x - 1 Loop Until x = 1 Range("A1:A30848").Sort key1:=Range("A1:A30848"), order1:=xlAscending Application.CutCopyMode = False End Sub
Sub PrePostTimeFrame() Dim mTime As String Dim tFrame As Integer Dim tCell, PreTime, PostTime As Range mTime = InputBox("Please input base time (hh:mm:ss):", "Base time") Set tCell = ActiveSheet.Columns("A:A").Find(What:=TimeValue(mTime), lookat:=xlPart) If tCell Is Nothing Then MsgBox ("Time not found.") Exit Sub End If tFrame = InputBox("Please input timeframe:", "Timeframe") On Error Resume Next ActiveWorkbook.Names("Pre").Delete ActiveWorkbook.Names("Post").Delete On Error GoTo 0 Set PreTime = tCell.Offset(-(128 * tFrame), 0) Range(PreTime, tCell.Offset(-1, 10)).Select ActiveWorkbook.Names.Add Name:="Pre", RefersTo:=Selection Set PostTime = tCell.Offset(128 * (tFrame - 1) + 127, 0) Range(tCell, PostTime.Offset(0, 10)).Select ActiveWorkbook.Names.Add Name:="Post", RefersTo:=Selection End Sub
Sub PrePostTimeFrame() Dim mTime As String Dim tFrame, newFrame As Integer Dim tCell, PreTime, PostTime As Range mTime = InputBox("Please input base time (hh:mm:ss):", "Base time", Format(Range("A1").Value, "hh:mm:ss")) If mTime = vbNullString Then Exit Sub Set tCell = ActiveSheet.Columns("A:A").Find(What:=TimeValue(mTime), lookat:=xlPart) If tCell Is Nothing Then MsgBox ("Time not found.") Exit Sub End If tFrame = InputBox("Please input timeframe:", "Timeframe") If tFrame = vbNullString Then Exit Sub On Error Resume Next ActiveWorkbook.Names("Pre").Delete ActiveWorkbook.Names("Post").Delete On Error GoTo 0 On Error GoTo SetPreTime Set PreTime = tCell.Offset(-(128 * tFrame), 0) Continue: Range(PreTime, tCell.Offset(-1, 10)).Select ActiveWorkbook.Names.Add Name:="Pre", RefersTo:=Selection Set PostTime = tCell.Offset(128 * (tFrame - 1) + 127, 0) Range(tCell, PostTime.Offset(0, 10)).Select ActiveWorkbook.Names.Add Name:="Post", RefersTo:=Selection MsgBox "The timeframe for the Pre range has been adjusted from " & tFrame & _ " seconds to " & newFrame & " seconds.", vbInformation, "Adjusted timeframe for Pre range" Exit Sub SetPreTime: Set PreTime = Range("A1") newFrame = (tCell.Row - 1) / 128 GoTo Continue End Sub
DON'T MISS