VBA Code to Loop, Find Missing Data and Perform Calculation

[MikeeRDX]

I have a list of data in column A starting at A2. This column contains data that are collected in increments of 30 minutes. There are missing data at random places and I need to write a procedure that will loop through, find the next missing data (blank row or rows), perform a calculation that takes the previous known data and add 30 minutes to it, then apply the result in the blank row. In some places there is only one blank row but in others there may be consecutive blank rows. This data is dynamic and can change but the incremental pattern is still the same. I'm new to VBA and having a very tough time understanding loops. Can someone help me with a VBA that will perform this procedure? Thank you.



10/1/2016 12:00


10/1/2016 12:30





10/1/2016 13:30








10/1/2016 15:00


10/1/2016 15:30


10/1/2016 16:00


10/1/2016 16:30

[mancubus]

?


Sub vbax_57608_fill_missing_values_in_column()

Dim i As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
If .Range("A" & i).Value = "" Then
.Range("A" & i).Value = DateAdd("n", 30, .Range("A" & i - 1).Value)
End If
Next i
End With

End Sub

[MikeeRDX]

?


Sub vbax_57608_fill_missing_values_in_column()

Dim i As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
If .Range("A" & i).Value = "" Then
.Range("A" & i).Value = DateAdd("n", 30, .Range("A" & i - 1).Value)
End If
Next i
End With

End Sub


Thank you so much! What about a procedure to loop through and if there is a gap between data (missing a time stamp of 30 mins increments), insert a blank row so the second loop can fill it in? So sorry. I just realized there is another requirement.

[mancubus]

you are welcome.
pls dont quote the whole post, especially if it is macro.

you mean there are no blank cells in column A but missing time stamps?

[MikeeRDX]

you are welcome.
pls dont quote the whole post, especially if it is macro.

you mean there are no blank cells in column A but missing time stamps?

Oh....sorry about the macro quote.

Yes. There are no blank rows but there are gaps in the data. I would like the loop to run through and determine if there is a break in the gap (meaning if the difference between top value and the bottom value is not equal to 30 minutes, then insert a row and plug in the missing data.

[mancubus]

?


Sub vbax_57608_v2_insert_missing_time_stamps_certain_interval()

Dim i As Long

On Error GoTo ExitProc
With Worksheets("Sheet1") 'change Sheet1 to suit
For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
If DateDiff("n", .Cells(i, 1).Value, .Cells(i - 1, 1).Value) <> -30 Then
.Cells(i, 1).EntireRow.Insert
.Cells(i, 1).Value = DateAdd("n", -30, .Cells(i + 1, 1).Value)
If i = 2 Then Exit For
i = i + 1
End If
Next i
End With

ExitProc:

End Sub

[MikeeRDX]

Thank you. Your code seems to work but it ran the job infinitely. How can I restrict it to just from the last row of data and work its way up?

[MikeeRDX]

The code seems to the take whatever the last data is, insert a blank row and add an entry minus 30 minutes. Which is fine but it neglects to see that some of the new added data have already existed. It seems to work from bottom up and creating new increments regardless they already existed.

[mancubus]

you'd better upload your workbook as explained in my signature.
alter sensitive, company specific data, if any.

care to provide the desired output in another sheet.