VBA定义工作表范围触发Runtime error 1004问题求助

错误根源 #

你的代码中，Master.Range("A2", Range("A2").End(xlDown))的第二个Range("A2")没有明确绑定到Master工作表对象，默认会引用当前活动工作表的A2单元格。如果此时活动工作表不是All_Plates_Mastersheet，就会出现跨工作表范围引用的冲突，触发1004错误。

修正后的基础代码 #

所有Range操作必须明确指定所属的工作表对象，修改后代码如下：

Sub defineRanges()

Dim WB As Workbook
Dim Master As Worksheet

Set WB = ThisWorkbook
Set Master = Worksheets("All_Plates_Mastersheet")

Dim PlateIDRng As Range
Dim ClientRng As Range
Dim ProjIDRng As Range
Dim PriorityRng As Range
Dim LimsStepRng As Range
Dim NumSamplesRng As Range
Dim LenNumRng As Range
Dim PhysLocRng As Range
Dim DateRecRng As Range

' 每个Range都绑定到Master工作表
Set PlateIDRng = Master.Range("A2", Master.Range("A2").End(xlDown))
Set ClientRng = Master.Range("B2", Master.Range("B2").End(xlDown))
Set ProjIDRng = Master.Range("C2", Master.Range("C2").End(xlDown))
Set PriorityRng = Master.Range("D2", Master.Range("D2").End(xlDown))
Set LimsStepRng = Master.Range("E2", Master.Range("E2").End(xlDown))
Set NumSamplesRng = Master.Range("F2", Master.Range("F2").End(xlDown))
Set LenNumRng = Master.Range("G2", Master.Range("G2").End(xlDown))
Set PhysLocRng = Master.Range("H2", Master.Range("H2").End(xlDown))
Set DateRecRng = Master.Range("I2", Master.Range("I2").End(xlDown))

End Sub

更可靠的优化版本 #

上述代码仍存在隐患：如果列中存在空行，End(xlDown)会提前停止；如果A2下方没有数据，会直接跳到工作表最后一行。建议改用从底部向上查找最后一行的方式，同时处理空列情况：

Sub defineRanges()
    Dim WB As Workbook
    Dim Master As Worksheet
    Dim lastRow As Long
    
    Set WB = ThisWorkbook
    Set Master = WB.Worksheets("All_Plates_Mastersheet") ' 绑定到指定工作簿，避免跨工作簿错误
    
    ' 从A列底部向上找最后一行数据
    lastRow = Master.Cells(Master.Rows.Count, "A").End(xlUp).Row
    ' 确保至少保留A2单元格（即使A列只有表头）
    If lastRow < 2 Then lastRow = 2
    
    Dim PlateIDRng As Range
    Dim ClientRng As Range
    Dim ProjIDRng As Range
    Dim PriorityRng As Range
    Dim LimsStepRng As Range
    Dim NumSamplesRng As Range
    Dim LenNumRng As Range
    Dim PhysLocRng As Range
    Dim DateRecRng As Range
    
    ' 用行号直接构建范围，更直观可靠
    Set PlateIDRng = Master.Range("A2:A" & lastRow)
    Set ClientRng = Master.Range("B2:B" & lastRow)
    Set ProjIDRng = Master.Range("C2:C" & lastRow)
    Set PriorityRng = Master.Range("D2:D" & lastRow)
    Set LimsStepRng = Master.Range("E2:E" & lastRow)
    Set NumSamplesRng = Master.Range("F2:F" & lastRow)
    Set LenNumRng = Master.Range("G2:G" & lastRow)
    Set PhysLocRng = Master.Range("H2:H" & lastRow)
    Set DateRecRng = Master.Range("I2:I" & lastRow)
End Sub

简化代码的写法（With语句） #

可以用With语句减少重复的Master引用，让代码更简洁：

Sub defineRanges()
    Dim WB As Workbook
    Dim Master As Worksheet
    Dim lastRow As Long
    
    Set WB = ThisWorkbook
    Set Master = WB.Worksheets("All_Plates_Mastersheet")
    
    With Master
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then lastRow = 2
        
        Dim PlateIDRng As Range
        Dim ClientRng As Range
        Dim ProjIDRng As Range
        Dim PriorityRng As Range
        Dim LimsStepRng As Range
        Dim NumSamplesRng As Range
        Dim LenNumRng As Range
        Dim PhysLocRng As Range
        Dim DateRecRng As Range
        
        Set PlateIDRng = .Range("A2:A" & lastRow)
        Set ClientRng = .Range("B2:B" & lastRow)
        Set ProjIDRng = .Range("C2:C" & lastRow)
        Set PriorityRng = .Range("D2:D" & lastRow)
        Set LimsStepRng = .Range("E2:E" & lastRow)
        Set NumSamplesRng = .Range("F2:F" & lastRow)
        Set LenNumRng = .Range("G2:G" & lastRow)
        Set PhysLocRng = .Range("H2:H" & lastRow)
        Set DateRecRng = .Range("I2:I" & lastRow)
    End With
End Sub

内容的提问来源于stack exchange，提问作者Michaela Danielle