excel - 根据2个单元格之间的差异插入n行

Question

我正在导出 excel 文件，通常它们缺少数据。所以我想创建一个脚本或其他东西，为每个丢失的数据在单元格之间插入一个空白行。例如：

[在此处输入图片描述][1]

正如您在 1569-1570 单元格之间看到的，每个数据 = 60 次测量缺少 5 小时/5 分钟，这意味着我需要 1569 和 1570 之间的 60 个空白行。由于导出格式，两个连续单元格之间的差异始终为 0.00347。有人能帮我吗？

我找到了一个并试图修改它，但它在最后一行显示除以 0。搜索后我发现VB不喜欢带小数的mod。有没有办法解决这个问题？

    Public Sub blank()
    
    
    Dim yourColumn As String
    Dim rowToStart As Integer
    Dim nameOfYourSheet As String
    Dim i As Integer
    Dim k As Double
    
    Dim currentvalue As Date
    Dim previousvalue As Date
    
    
    'EDIT VALUES HERE
    yourColumn = "B"
    rowToStart = 4
    k = 0.00347
    nameOfYourSheet = "02 February calculations"
    
    With ThisWorkbook.Sheets(nameOfYourSheet)
        'For each cell in the column from the end
        For i = .Cells(.Rows.Count, yourColumn).End(xlUp).Row To rowToStart + 1 Step -1
            currentvalue = (.Range(yourColumn & i).Value)
            previousvalue = (.Range(yourColumn & i).Offset(-1, 0).Value)
    
            'If the difference with the previous > k
            If ((currentvalue - previousvalue) > k) Then
                'Add rows
                .Rows(i).EntireRow.Insert Shift:=((currentvalue - previousvalue) Mod (k))
            End If
        Next i
    End With
    
    End Sub


  [1]: https://i.stack.imgur.com/MZPRS.png

编辑：我一直在挖掘这个，现在它运行了，但它只在所有单元格之间插入了 1 行。我想这是因为 currentvalue 不能正常工作，因为它给出了错误的值。任何帮助，将不胜感激。

Public Sub blank()


Dim yourColumn As String
Dim rowToStart As Integer
Dim nameOfYourSheet As String
Dim i As Integer
Dim k As Double
Dim k1 As Double
Dim m As Double
Dim n As Double


Dim currentvalue As Date
Dim previousvalue As Date


'EDIT VALUES HERE
yourColumn = "B"
rowToStart = 4
k = 0.003472
nameOfYourSheet = "02 February calculations"

With ThisWorkbook.Sheets(nameOfYourSheet)
    'For each cell in the column from the end
    For i = .Cells(.Rows.Count, yourColumn).End(xlUp).Row To rowToStart + 1 Step -1
        currentvalue = (.Range(yourColumn & i).Value)
        previousvalue = (.Range(yourColumn & i).Offset(-1, 0).Value)
        
        m = (currentvalue - previousvalue) * 100000
        k1 = k * 100000
        
        'If the difference with the previous > k1
        If (m > k1) Then
            n = m / k1
            'Add rows
            .Rows(i).EntireRow.Insert Shift:=n
        End If
        
    Next i
End With

标签： excelrowcellblank-linescript