时间戳

首页 > 解决方案 > 用最后一行的值填充 Column 中的所有行。VBA 错误。未设置对象变量或 With 块变量

excel - 用最后一行的值填充 Column 中的所有行。VBA 错误。未设置对象变量或 With 块变量

问题描述

我有一个宏，它检查表的第 2 列中最后填充的行的值，并用相同的值填充列的所有先前行（空白或其他行）。

此代码工作正常：

Sub Test1()
    Dim cel As Range
    Dim JulyData2 As Range
    Dim ws As Worksheet
    Dim LRR2 As Range
    Dim LRC2 As Long
Set ws = ThisWorkbook.Worksheets("Tracker")
Set JulyData2 = ws.ListObjects("Tb_July2021").ListColumns(2).DataBodyRange
Set LRR2 = JulyData2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
LRC2 = JulyData2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Application.ScreenUpdating = False

For Each cel In JulyData2.Cells
If cel.Row < LRC2 Then cel.Value = LRR2.Value
Next cel

    Application.ScreenUpdating = True
End Sub

现在我想要的是这段代码从第 2 列开始循环遍历表中的所有列，即检查从第 2 列到最后一列的表的每一列中最后填充的行的值并填充所有前面具有相同值的相应列的行。

我试过这段代码：

Sub Test2()
Dim tblJuly As ListObject
Dim x As Long

Set tblJuly = ActiveSheet.ListObjects("Tb_July2021")

    Application.ScreenUpdating = False

  For x = 2 To tblJuly.ListColumns.Count
Dim LRR As Range 
Dim cel As Range
    Set LRR = tblJuly.ListColumns(x).DataBodyRange.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

  For Each cel In tblJuly.ListColumns(x).DataBodyRange.Cells
If cel.Row < LRR.Row Then cel.Value = LRR.Value
  Next cel

  Next x

    Application.ScreenUpdating = True

End Sub

但是这段代码给了我一个运行时错误'91'对象变量或未设置块变量

它在此行显示错误：

If cel.Row < LRR.Row Then

我哪里错了？我该如何纠正？

标签： excelvba

解决方案

该代码现在有效。它跳过导致错误的空列并继续下一列。

Sub Test2()
    Dim tblJuly As ListObject
    Dim x As Long
    Dim LRR As Range 
    Dim cel As Range

    Set tblJuly = ActiveSheet.ListObjects("Tb_July2021")

    Application.ScreenUpdating = False

    For x = 2 To tblJuly.ListColumns.Count

    Set LRR = tblJuly.ListColumns(x).DataBodyRange.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Not LRR Is Nothing Then
       For Each cel In tblJuly.ListColumns(x).DataBodyRange.Cells
          If cel.Row < LRR.Row Then cel.Value = LRR.Value
              Next cel
          End If
    Next x   
    Application.ScreenUpdating = True    
End Sub

推荐阅读