wielandvds
04-12-2018, 03:05 AM
Hello,
I have a dataset consisting of a one-year PV inverter's input power. The sample time for this dataset isn't constant as it stops logging data in the evening (when depends on the season so it's irregular) and continues again in the morning (also irregular time). I'm trying to create a VBA macro that can insert rows for every 5 minutes between the last point at night and the first point in the morning so I can fill the rows up with zeros to calculate temperature cycle periods. I've created the script below but it gives me an error considering the Range().EntireRow.Insert. Could someone please help me out with this problem?
Sub insertingRows()
Dim counter
Dim hourDiff
Dim minuteDiff
Dim newRows
Dim test
Dim curHour
Dim prevHour
Dim curCell
Dim prevCell
hourDiff = 0
minuteDiff = 0
newRows = 0
Set prevCell = Worksheets("Sheet1").Cells(1, 1)
For counter = 2 To 64822 'Start to compare cell A2 with cell A1
Set curCell = Worksheets("Sheet1").Cells(counter, 1)
curHour = Hour(curCell)
prevHour = Hour(prevCell)
If curHour - prevHour > 1 Then 'If the night passed, add rows untill the next measurement
hourDiff = 24 - Hour(curCell) + Hour(prevCell)
minuteDiff = Minute(prevCell) - Minute(curCell)
newRows = (hourDiff * 12) + Round((minuteDiff / 5), [0]) - 1 'A row is added every 5 mins
Set finalCell = Worksheets("Sheet1").Cells(curCell.Row + newRows, 1)
Range("curCell:finalCell").EntireRow.Insert 'Error, wrong function? Bad range?
End If
prevCell = Worksheets("Sheet1").Cells(counter, 1)
Next
End Sub
I have a dataset consisting of a one-year PV inverter's input power. The sample time for this dataset isn't constant as it stops logging data in the evening (when depends on the season so it's irregular) and continues again in the morning (also irregular time). I'm trying to create a VBA macro that can insert rows for every 5 minutes between the last point at night and the first point in the morning so I can fill the rows up with zeros to calculate temperature cycle periods. I've created the script below but it gives me an error considering the Range().EntireRow.Insert. Could someone please help me out with this problem?
Sub insertingRows()
Dim counter
Dim hourDiff
Dim minuteDiff
Dim newRows
Dim test
Dim curHour
Dim prevHour
Dim curCell
Dim prevCell
hourDiff = 0
minuteDiff = 0
newRows = 0
Set prevCell = Worksheets("Sheet1").Cells(1, 1)
For counter = 2 To 64822 'Start to compare cell A2 with cell A1
Set curCell = Worksheets("Sheet1").Cells(counter, 1)
curHour = Hour(curCell)
prevHour = Hour(prevCell)
If curHour - prevHour > 1 Then 'If the night passed, add rows untill the next measurement
hourDiff = 24 - Hour(curCell) + Hour(prevCell)
minuteDiff = Minute(prevCell) - Minute(curCell)
newRows = (hourDiff * 12) + Round((minuteDiff / 5), [0]) - 1 'A row is added every 5 mins
Set finalCell = Worksheets("Sheet1").Cells(curCell.Row + newRows, 1)
Range("curCell:finalCell").EntireRow.Insert 'Error, wrong function? Bad range?
End If
prevCell = Worksheets("Sheet1").Cells(counter, 1)
Next
End Sub