AW: Zeile einfügen Daten übernehmen
17.05.2021 18:09:28
Nepumuk
Hallo Werner,
teste mal:
Code:
[Cc]
Option Explicit
Public Sub InsertMissingRows()
Dim lngRow As Long, lngStartMinute As Long, lngRuningtMinute As Long
Dim dblQuater As Double
Dim dtmNextTime As Date, dtmPreviousTime As Date
Dim avntTemp As Variant
dblQuater = Round(CDbl(TimeSerial(0, 15, 0)), 4)
With Worksheets("RMB Report")
lngStartMinute = Minute(CDate(Split(.Cells(2, 1).Value, " ")(1)))
For lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row To 3 Step -1
lngRuningtMinute = Minute(CDate(Split(.Cells(lngRow, 1).Value, " ")(1)))
If lngRuningtMinute <> lngStartMinute And lngRuningtMinute <> lngStartMinute + 15 And _
lngRuningtMinute <> lngStartMinute + 30 And lngRuningtMinute <> lngStartMinute + 45 Then
Call .Rows(lngRow).Delete
End If
Next
For lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row To 3 Step -1
avntTemp = Split(.Cells(lngRow, 1).Value, " ")
dtmNextTime = CDate(Left$(avntTemp(0), 10) & " " & avntTemp(1))
avntTemp = Split(.Cells(lngRow - 1, 1).Value, " ")
dtmPreviousTime = CDate(Left$(avntTemp(0), 10) & " " & avntTemp(1))
If Round(CDbl(dtmNextTime - dtmPreviousTime), 4) > dblQuater Then
Call .Rows(lngRow).Insert
.Cells(lngRow, 1).Value = Format$(dtmPreviousTime + _
TimeSerial(0, 15, 0), "dd.mm.yyyy, Hh:Nn ") & "Uhr"
Call .Range(.Cells(lngRow - 1, 2), .Cells(lngRow - 1, 10)).Copy( _
Destination:=.Cells(lngRow, 2))
lngRow = lngRow + 2
End If
Next
End With
End Sub
Gruß
Nepumuk