Hello all,
I am looking for some help either A) troubleshooting an existing macro or B) finding another (better?) method for solving the same problem.
Because my write-up has proven to be rather lengthy to explain, here is the "TLDR" version: I have a macro that is supposed to fill in gaps in a data set that has a time sequence. It has some strange bugs and isn't functioning properly. It is included at the bottom of the post. Any idea what is wrong? Should I be doing this a different way? Note: This problem has persisted between two computers, one using Windows 7 and Excel 2010, and another using Windows 7 and Excel 2013.
For those of you who are interested in the full explanation and everything I know so far, here goes:
I am working with large data sets (hundreds of thousands of lines+) that include a time stamp for each line. Each time stamp occurs after 1 second. Due to what I believe is hardware limitations with our data collectors, there is a missing value every so often (~90 line increments). To have a continuous time series I have been working with a macro that inserts a row for each missing time value and inserts the proper time stamp. Note: This problem has persisted between two computers, one using Windows 7 and Excel 2010, and another using Windows 7 and Excel 2013.
For example:
<tbody>
</tbody>
Between line 5 and line 6 there are 5 missing data points (and therefore a discontinuous time series.) After running my macro, this same data looks like this:
<tbody>
</tbody>
This is working as intended.
Now here is the problem:
While running this macro on large data sets (100,000-750,000 lines) I encountered some large gaps in my data that this macro did NOT fill in with time stamps. To illustrate I've included an example below.
The example below shows gaps in time between the highlighted time stamps prior to running the macro to fill them in.
<tbody>
</tbody>
The example below shows some filled gaps in green but then there is a 7 minute gap between the last two red highlighted time stamps where the macro did not fill in missing time stamps.
<tbody>
</tbody>
Something interesting to note here: the time value 18:59:09 does not exist in the raw data, and so there should be no "6" next to it; the same goes for the time stamp 18:58:16, which should also not have a "5" next to it as it also doesn't exist in the raw data. These values next to the time stamps appear to be shifted from their original cells: "5" used to belong to 19:08:08 and "6" used to belong to 19:08:09. Also, 19:08:08 and 19:08:09 don't even exist in the data set after the macro has been run. So...we are quite lost at this point and any help would be greatly appreciated. I've included the macro below.
Sub addmissingseconds()
'This workbook has a very crude macro that inserts rows for missing seconds.
'The date and time must be in column A.
'The macro ignores the first 3 lines to allow for a header. We could make that two lines if better.
'The spreadsheet must have the date and time in column A.
'The macro will add the missing times in column A, and blank values in the other columns.
'With this macro open, you can open a spreadsheet of data and hit CTRL-SHIFT-I to insert the values.
'This spreadsheet should not be fully trusted. Check results and report back to Lonny.
Dim i As Long, x As Long
'step from end to the third row
For i = Range("a" & Rows.Count).End(xlUp).Row To 3 Step -1
x = Val(Format$(Cells(i, 1).Value - Cells(i - 1, 1).Value, "s"))
If x > 1 Then
Rows(i + 1).Resize(x - 1).Insert
With Cells(i, 1).Resize(x + 1)
.Formula = "=r[-1]c+""0:00:01"""
.Value = .Value
'this should add in the new row with just column A. Use Resize(, 3) for B and C
With .Offset(, 1).Resize(, 1)
'.Formula = "=r[-1]c"
'.Value = .Value
End With
End With
End If
Next
End Sub
Thank you very much for any help you might have to offer!
-Josiah
I am looking for some help either A) troubleshooting an existing macro or B) finding another (better?) method for solving the same problem.
Because my write-up has proven to be rather lengthy to explain, here is the "TLDR" version: I have a macro that is supposed to fill in gaps in a data set that has a time sequence. It has some strange bugs and isn't functioning properly. It is included at the bottom of the post. Any idea what is wrong? Should I be doing this a different way? Note: This problem has persisted between two computers, one using Windows 7 and Excel 2010, and another using Windows 7 and Excel 2013.
For those of you who are interested in the full explanation and everything I know so far, here goes:
I am working with large data sets (hundreds of thousands of lines+) that include a time stamp for each line. Each time stamp occurs after 1 second. Due to what I believe is hardware limitations with our data collectors, there is a missing value every so often (~90 line increments). To have a continuous time series I have been working with a macro that inserts a row for each missing time value and inserts the proper time stamp. Note: This problem has persisted between two computers, one using Windows 7 and Excel 2010, and another using Windows 7 and Excel 2013.
For example:
<tbody> </tbody> |
<tbody>
</tbody>
Between line 5 and line 6 there are 5 missing data points (and therefore a discontinuous time series.) After running my macro, this same data looks like this:
<tbody> </tbody> |
<tbody>
</tbody>
This is working as intended.
Now here is the problem:
While running this macro on large data sets (100,000-750,000 lines) I encountered some large gaps in my data that this macro did NOT fill in with time stamps. To illustrate I've included an example below.
The example below shows gaps in time between the highlighted time stamps prior to running the macro to fill them in.
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
The example below shows some filled gaps in green but then there is a 7 minute gap between the last two red highlighted time stamps where the macro did not fill in missing time stamps.
<tbody> </tbody> |
<tbody>
</tbody>
Something interesting to note here: the time value 18:59:09 does not exist in the raw data, and so there should be no "6" next to it; the same goes for the time stamp 18:58:16, which should also not have a "5" next to it as it also doesn't exist in the raw data. These values next to the time stamps appear to be shifted from their original cells: "5" used to belong to 19:08:08 and "6" used to belong to 19:08:09. Also, 19:08:08 and 19:08:09 don't even exist in the data set after the macro has been run. So...we are quite lost at this point and any help would be greatly appreciated. I've included the macro below.
Sub addmissingseconds()
'This workbook has a very crude macro that inserts rows for missing seconds.
'The date and time must be in column A.
'The macro ignores the first 3 lines to allow for a header. We could make that two lines if better.
'The spreadsheet must have the date and time in column A.
'The macro will add the missing times in column A, and blank values in the other columns.
'With this macro open, you can open a spreadsheet of data and hit CTRL-SHIFT-I to insert the values.
'This spreadsheet should not be fully trusted. Check results and report back to Lonny.
Dim i As Long, x As Long
'step from end to the third row
For i = Range("a" & Rows.Count).End(xlUp).Row To 3 Step -1
x = Val(Format$(Cells(i, 1).Value - Cells(i - 1, 1).Value, "s"))
If x > 1 Then
Rows(i + 1).Resize(x - 1).Insert
With Cells(i, 1).Resize(x + 1)
.Formula = "=r[-1]c+""0:00:01"""
.Value = .Value
'this should add in the new row with just column A. Use Resize(, 3) for B and C
With .Offset(, 1).Resize(, 1)
'.Formula = "=r[-1]c"
'.Value = .Value
End With
End With
End If
Next
End Sub
Thank you very much for any help you might have to offer!
-Josiah
