VBA新手求助：设置Worksheet的Range对象时出现1004错误

Hey there, let's break down why you're hitting that 1004 error and fix it up. The problem stems from two key issues in your range assignment lines:

1. Unqualified Range References #

When you write Range("A2") without specifying which worksheet it belongs to, VBA defaults to using the active sheet at runtime. If the active sheet isn't "MM Limits" or "PivotTable" when those lines run, you're trying to combine ranges from different worksheets into one—this mismatch triggers the 1004 error.

2. Incorrect Syntax for the End Method #

You used endxldown instead of the valid syntax: End(xlDown) (note the parentheses and proper capitalization of the constant).

Corrected Code #

Here's the fixed version of your subroutine:

Sub MMMatch()
    Dim oCell As Range
    Dim r_out As Range
    Dim r_in As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = Worksheets("MM Limits")
    Set ws2 = Worksheets("PivotTable")
    
    ' Fully qualify all ranges with their respective worksheets
    Set r_out = ws1.Range(ws1.Range("A2"), ws1.Range("A2").End(xlDown))
    Set r_in = ws2.Range(ws2.Range("D2"), ws2.Range("D2").End(xlDown))
End Sub

Bonus: More Reliable Last Row Detection #

A quick heads-up: End(xlDown) stops at the first empty cell in the column. If your data has blank gaps, this might not capture all rows. A more robust way to get the last used row is:

' For ws1 column A
Dim lastRowWs1 As Long
lastRowWs1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
Set r_out = ws1.Range("A2:A" & lastRowWs1)

' For ws2 column D
Dim lastRowWs2 As Long
lastRowWs2 = ws2.Cells(ws2.Rows.Count, "D").End(xlUp).Row
Set r_in = ws2.Range("D2:D" & lastRowWs2)

This starts from the bottom of the worksheet and moves up to the last non-empty cell, ensuring you capture all your data even if there are gaps.

内容的提问来源于stack exchange，提问作者David Lau