Copying time fields down Closed

Question

Hello, 
I am attempting to put the time starting at 12:32:44 AM for 128 rows and click up the time by one second to 12:32:45 AM for the next 128 rows, and do this for 240 seconds of data. 
I have attempted to do it by hand but the risk of not getting exactly 128 rows is large with manual selection, plus it would take forever as there are 30720 rows. Is there an easy way to do this? time has to advance for the minute as well, when the time comes (pun intended.)

System Configuration: Windows 7/ Chrome 67.0.3396.99

TrowaD · Answer

Hi Somnonaut,

Result will be placed in column A. Clear the column and then run the code:
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

PS. You will end up with 128 * 241 = 30848 rows to get the described result.

Best regards,
Trowa

Somnonaut · Answer

Absolutely fantastic help. THANK YOU SO MUCH!!

Now, for the next part.

Somnonaut · Answer

I am trying to make a proof of concept example. I have my 128 samples per second over 240 seconds. and now I am trying to build a movable array of cells, 1 cell high, by 11 columns with various permutation of averaging Pre array location and POST array location, with each span (pre and post) being of the same number of rows. Meaning,  the home array can be anywhere, and as the array is dragged open X rows downward, the array's range (for each of the 11 home cells across the 11 columns, now spans backwards the same number of rows as the dragging expanded the array forwards. So if we are row 1280 (10 seconds into the file) the array's range is set to look into the past 10 seconds (1:1280), while the looking forward range is 1281:2560 (10 seconds into the future). BUT...as I move the array forward, the range backward has to be updated to reflect the same 10 second pre and post, back and forward). Not only should the moving of the array be hard and fast at 10 second pre and post, but...the array should be expandable, so that if the user wanted to look at a n array that spanned any number of seconds > 10sec. (in 128 increments per second) they should be able to either hgihlight the time of interest now located in column 1 (thank you) or have some other mechanism to "grab" the desired timeframe. 
Here is the excel file if you wish to take a look, and I have graphics of how I wish (in my mind) the "array" function in the real-world. The array I built starts on F1283 and is written hard at 10 seconds. I need to make that a flexible timeline (range). I hope I am conveying this cogently. 
https://drive.google.com/drive/folders/1y0n4gbMFsyIZn3TxROlh8XjqlYDxWQKw?usp=sharing

TrowaD · Answer

Hi Somnonaut,

I'm struggling to figure out what you are trying to achieve.

This is what I think you are after:
When you select A1280 with a time of 12:32:53, you want that selection to expand to 10 sec. before and after that time and add 10 columns.
 So selecting A1280 will result in a selection of A1:K2560.

Or do you want to create named ranges PRE and POST, with PRE being A1:K1280 and POST being A1281:K2560?

I'm sure I'm missing something. Just wanted to let you know where I'm at with my thought process.

Best regards,
Trowa

TrowaD · Answer

Hi Somnonaut,

Could you give your thoughts on the code below. It will ask you for the base time and timeframe. If for example you enter:
00:32:50 as base time and 
3 for timeframe 
then the named range for 
'Pre' will be 00:32:47 - 00:32:49 and
'Post'will be 00:32:50 - 00:32:52

Column A:K will be included in those named ranges.

Here is the code:
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

Best regards,
Trowa

TrowaD · Answer

The previous code would provide an error when there are not enough seconds/rows left to determine the Pre range.

The code below will adjust the time frame, so that the Pre range can still be allocated.
 
Here is the code:
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

Copying time fields down

6 responses

Similar discussions

Subscribe To Our Newsletter!