VBA遍历工作表设置区域填充色时触发错误438的问题

问题描述 #

我有三个工作表：Hoja1、Hoja2、Hoja3，每个工作表均包含A1:Bx格式的两列数据（行数随工作表不同而变化）。我已验证获取最后一行（LastRow）与最后一列（LastColumn）的代码可正常运行（通过填充颜色确认），但尝试填充Range(Cells(2, LastColumn), Cells(LastRow, LastColumn))（即从C列到对应最后一行的整列区域）时，触发了错误438。

原VBA代码 #

Public Sub ponerAlgoEnCadaHoja()

Dim LastRowVisibleCategorie As Integer
Dim VisibleCategorieList As Range
Dim VC As Range
Dim Cell As Range
Dim RecategorizeListRange As Range

LastRowVisibleCategorie = Cells(Rows.Count, "E").End(xlUp).Row
Set VisibleCategorieList = Range(Cells(2, "E"), Cells(LastRowVisibleCategorie, "E"))

For Each VC In VisibleCategorieList
    LastRowVC = Sheets(VC.Value).Cells(Rows.Count, "A").End(xlUp).Row
   Sheets(VC.Value).Cells(LastRowVC, "A").Interior.ColorIndex = 37
    LastColumnVC = Sheets(VC.Value).Cells(2, "A").End(xlToRight).Column + 1
   Sheets(VC.Value).Cells(2, LastColumnVC).Interior.ColorIndex = 37
    
    Set RecategorizeListRange = Sheets(VC.Value).Range(Cells(2, LastColumnVC), Cells(LastRowVC, LastColumnVC))
    Sheets(VC.Value).RecategorizeListRange.Interior.ColorIndex = 37
Next

End Sub

错误原因分析 #

• Cells对象未指定工作表上下文：创建RecategorizeListRange时，Cells(2, LastColumnVC)和Cells(LastRowVC, LastColumnVC)默认引用当前活动工作表，而非目标工作表Sheets(VC.Value)，导致Range对象父对象不匹配，触发错误438。
• Range对象调用语法错误：最后一行Sheets(VC.Value).RecategorizeListRange写法错误，RecategorizeListRange已经绑定到目标工作表，无需再通过工作表前缀调用。

修正后的代码 #

Public Sub ponerAlgoEnCadaHoja()
    Dim LastRowVisibleCategorie As Integer
    Dim VisibleCategorieList As Range
    Dim VC As Range
    Dim RecategorizeListRange As Range
    Dim ws As Worksheet ' 新增工作表变量，简化上下文引用
    
    LastRowVisibleCategorie = Cells(Rows.Count, "E").End(xlUp).Row
    Set VisibleCategorieList = Range(Cells(2, "E"), Cells(LastRowVisibleCategorie, "E"))
    
    For Each VC In VisibleCategorieList
        ' 绑定目标工作表到变量，避免重复书写
        Set ws = Sheets(VC.Value)
        
        LastRowVC = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        ws.Cells(LastRowVC, "A").Interior.ColorIndex = 37
        
        LastColumnVC = ws.Cells(2, "A").End(xlToRight).Column + 1
        ws.Cells(2, LastColumnVC).Interior.ColorIndex = 37
        
        ' 所有Cells对象都通过ws限定上下文，确保引用目标工作表
        Set RecategorizeListRange = ws.Range(ws.Cells(2, LastColumnVC), ws.Cells(LastRowVC, LastColumnVC))
        ' 直接操作已绑定的Range对象
        RecategorizeListRange.Interior.ColorIndex = 37
    Next
End Sub

关键修改说明 #

• 新增ws工作表变量，将目标工作表绑定到变量，简化代码同时避免上下文混淆。
• 所有Cells、Rows对象都通过ws前缀限定，确保引用目标工作表的单元格，而非活动工作表。
• 移除Sheets(VC.Value).前缀直接调用RecategorizeListRange，因为该对象已绑定到目标工作表。

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir