Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copying time fields down [Closed]

Report
Posts
6
Registration date
Sunday July 1, 2018
Status
Member
Last seen
August 13, 2019
 -
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 -
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.)

See more

6 replies

Answer 1 / 6
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Answer 2 / 6
Posts
6
Registration date
Sunday July 1, 2018
Status
Member
Last seen
August 13, 2019
Absolutely fantastic help. THANK YOU SO MUCH!!

Now, for the next part.
Answer 3 / 6
Posts
6
Registration date
Sunday July 1, 2018
Status
Member
Last seen
August 13, 2019
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
Answer 4 / 6
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Report
Posts
6
Registration date
Sunday July 1, 2018
Status
Member
Last seen
August 13, 2019
Thank you so much for spending time on my concern.

In actuality, we are trying to convert a visual selection process (as explained in the snippet of the rule manual and my graphics) with a purely numerical, tabular selection process. While also searching for an answer I did come across this selection process that changes an array with date pulldowns. Could this be used with the time field you graciously made?

https://www.youtube.com/watch?v=sHfWRb2yUrM

This method of selection does two things:
1) give a chart of the selection, which would confirm the selected period as compared to the whole 240 sec recording image;
2) it could be used to change the array(s) as you mention in the second method.

Q: Would pre and post not be an array each? And could we not just name the arrays "pre" and "post" with the pertinent columns assigned to each? Or do arrays have to be contiguous? I am lost and hunt youtube to try and glean what capabilities are within Excel. I truly welcome and appreciate your wisdom.
Report
Posts
6
Registration date
Sunday July 1, 2018
Status
Member
Last seen
August 13, 2019
I just would like to point out that you mention 10 sec pre and post, but the user should be able to select more than 10 sec. It is not a hard and fast timeframe. It is the minimum timeframe an event can be.
Answer 5 / 6
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Answer 6 / 6
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Ask a question