excel - 如何使用引用单元格中的值设置范围？

Question

我正在尝试构建一个宏以在填充单元格结束后用零填充一定数量的空白单元格。此宏循环遍历工作簿中的所有工作表(第一个工作表除外，它在代码中命名)。我的代码如下:

    NumSh = ThisWorkbook.Worksheets.Count  

    For Each sh In ActiveWorkbook.Worksheets
    First = LastRow(sh)

    If IsError(Application.Match(sh.Name, _
        Array("ECG_Log (root)"), 0)) Then
        For i = 1 To (NumSh - 1)
            LastVal = i + 4
            Last = Worksheets("ECG_Log (root)").Cells(LastVal, 12).Value
            'MsgBox (First)
            Set ZerRng = sh.Range(Cells(First, 1), Cells(Last, 2))
               '^^this is where is throws the 1004: range of object failed 
                   'error
            Range(ZerRng).Value = 0
        Next i
    End If
 Next

End Sub



Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
                        After:=sh.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
On Error GoTo 0
End Function

理想情况下，我想打开每个工作表，找到最后一行(“第一行”)，并定义从最后一行到第一张工作表上单元格的值(“最后一个”)的范围。似乎为 First 和 Last 找到了正确的值，但是在尝试为零定义我的范围时我仍然遇到错误。有什么建议么？

Answer 1

填零

Sub FillZeros()
    
    Const cSheet As String = "ECG_Log (root)" ' Source Worksheet Name
    Const cFer As Long = 4          ' Source Above First Row Number
    Const cCol As Variant = 12      ' Source Column Letter/Number ("L")
    Const cCols As String = "A:B"   ' Target Columns Address
    
    Dim ws As Worksheet   ' Target Worksheet
    Dim Fer As Long       ' Target First Empty Row
    Dim Lr As Long        ' Target Last Row
    Dim i As Long         ' Source Row Counter
    
    ' Loop through worksheets in this workbook (workbook containing this code).
    For Each ws In ThisWorkbook.Worksheets
        ' Check if name of current Target Worksheet is NOT equal to Source
        ' Worksheet Name.
        If ws.Name <> cSheet Then
            ' Increase (count) Source Row.
            i = i + 1
            ' Calculate current Target First Empty Row using LastRow function.
            Fer = LastRow(ws) + 1
            ' Calculate Target Last Row i.e. retrieve value from cell at
            ' current row and Source Column of Source Worksheet.
            Lr = ThisWorkbook.Worksheets(cSheet).Cells(i + cFer, cCol).Value
            ' In Current Target Columns
            With ws.Columns(cCols)
                ' Prevent error if already done.
                On Error Resume Next
                    ' Calcutate Target Range.
                    ' Write zeros to Target Range.
                    .Rows(Fer).Resize(Lr - Fer + 1) = 0
                ' Reset error.
                On Error GoTo 0
            End With
        End If
    Next

End Sub

Function LastRow(ws As Worksheet) As Long
    On Error Resume Next
        ' The After argument's default parameter is the left upper cell of
        ' Expression (range) i.e. A1 in this case.
        ' The LookAt and MatchCase arguments are not important because of
        ' What:="*".
        LastRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    On Error GoTo 0
End Function