VBA按条件复制合并Range时触发运行时错误424求助

Alright, let's break down exactly why that highlighted line is throwing the "Object Required" (Error 424) issue, and walk through the fixes step by step.

Key Causes of the Error #

1. Missing Object Declaration for CopyRng12 #

The biggest issue is that you never declared CopyRng12 as a Range object. In VBA, if you skip declaring a variable with Dim, it defaults to a Variant type. When you try to check If Not CopyRng12 Is Nothing, you're attempting to treat an uninitialized Variant (which is Empty, not Nothing) as an object—this directly triggers the 424 error.

2. Potential Uninitialized Variable After Loop #

Even if you fix the declaration, if none of the rows in your loop match the ws6.Cells(q, "F").Value = "G1" condition, CopyRng12 will remain Nothing. Later when you run CopyRng12.Copy, you'll hit the same error again because there's no object to copy.

3. Unnecessary Activate/Select Calls (Bonus Issue) #

While not the direct cause of your error, using Activate and Select makes your code fragile—it can break if the wrong sheet is active. We'll clean that up too.

Corrected Code #

Here's the fixed version of your sub with all issues addressed:

Sub grouping()
    ' Declare all variables explicitly (always use Option Explicit at the top of modules to enforce this!)
    Dim ws6 As Worksheet, ws7 As Worksheet
    Dim wsmm As Worksheet, wbmm As Workbook
    Dim LastRowu As Long, LastRowb As Long, LastRowb1 As Long
    Dim q As Long
    Dim CopyRng12 As Range ' Critical: Declare as Range object
    
    Set ws6 = Workbooks("A.xlsx").Worksheets("X1")
    Set ws7 = Workbooks("B.xlsx").Worksheets("X2")
    
    ' Qualify Rows.Count with the worksheet to avoid active sheet issues
    LastRowu = ws6.Cells(ws6.Rows.Count, "B").End(xlUp).Row
    LastRowb = ws7.Cells(ws7.Rows.Count, "K").End(xlUp).Row
    LastRowb1 = ws7.Cells(ws7.Rows.Count, "L").End(xlUp).Row
    
    Application.Calculation = xlAutomatic
    
    ws6.Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ws6.Range("E2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[B.xlsx]X2'!C11:C12,2,0)"
    ws6.Range("E2").AutoFill Destination:=ws6.Range("E2:E" & LastRowu), Type:=xlFillDefault
    
    With ws6.UsedRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
    
    ws6.Cells.Replace "#N/A", "Company Code Not Found", xlWhole
    
    ' Avoid Select/Activate - work directly with objects
    ws6.Columns("D:D").Copy
    ws6.Columns("A:A").Insert Shift:=xlToRight
    
    For q = LastRowu - 1 To 1 Step -1
        If ws6.Cells(q, "F").Value = "G1" Then
            If Not CopyRng12 Is Nothing Then
                Set CopyRng12 = Application.Union(CopyRng12, ws6.Rows(q))
            Else
                Set CopyRng12 = ws6.Rows(q)
            End If
        End If
    Next q
    
    ' Check if we actually found any rows to copy before proceeding
    If Not CopyRng12 Is Nothing Then
        Set wbmm = Workbooks("G1.xlsx")
        Set wsmm = wbmm.Worksheets("X1")
        
        ' Clear and paste directly without activating sheets
        wbmm.Worksheets("X2").ClearContents
        CopyRng12.Copy Destination:=wbmm.Worksheets("X2").Range("A1")
        Application.CutCopyMode = False
    Else
        ' Handle the case where no matching rows were found
        MsgBox "No rows with value 'G1' found in column F.", vbInformation
    End If
End Sub

What Changed? #

• Added Dim CopyRng12 As Range: Properly declares the variable as a Range object, so VBA recognizes it as an object and allows Is Nothing checks.
• Qualified Rows.Count: Uses ws6.Rows.Count instead of just Rows.Count to ensure we're counting rows on the correct sheet, not the active one.
• Removed Activate/Select: Rewrote those sections to work directly with worksheet objects, making the code more stable.
• Added a safety check: Before trying to copy, we verify CopyRng12 isn't Nothing—this prevents errors if no matching rows are found, and shows a helpful message instead.
• Direct paste destination: Uses the Destination parameter in Copy to paste without needing to activate the target sheet.

Pro tip: Always add Option Explicit at the very top of your VBA module. This forces you to declare all variables, which catches issues like missing declarations before you even run the code.

内容的提问来源于stack exchange，提问作者yashika vaish