VBA宏报错‘424对象要求’：基于变量设置行范围问题求助

Error Cause #

The 424 Object Required error happens because var stores the value of Range("B1"), not the Range object itself. You can’t call .Offset on a plain value—you need a valid Range reference for that operation.

Corrected Base Code (Single Worksheet) #

First, fix the initial macro to properly handle range references and avoid unreliable Select/Activate calls:

Sub Denest()
    Dim wsDenest As Worksheet, wsIncoming As Worksheet
    Dim startRow As Long, numRows As Long
    Dim sourceRange As Range
    
    ' Set direct worksheet references (no more selecting sheets)
    Set wsDenest = ThisWorkbook.Sheets("Denest")
    Set wsIncoming = ThisWorkbook.Sheets("Incoming")
    
    ' Clear existing data in Denest sheet
    wsDenest.Rows("3:" & wsDenest.Rows.Count).ClearContents
    
    ' Get user inputs (start row from B1, row count from B2)
    startRow = wsIncoming.Range("B1").Value
    numRows = wsIncoming.Range("B2").Value ' Adjust this cell to your input source
    
    ' Define source range with offset (example: startRow + 2 for rows 57:61 when startRow=55, numRows=5)
    Set sourceRange = wsIncoming.Rows(startRow + 2 & ":" & startRow + 2 + numRows - 1)
    
    ' Copy and paste to target sheet
    sourceRange.Copy
    wsDenest.Rows("3").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Application.CutCopyMode = False
End Sub

Full Implementation for 6 Worksheets with Different Offsets #

To handle 6 target sheets with varying row offsets, use a loop with arrays for sheet names and offsets—this keeps code clean and scalable:

Sub DenestMultiSheet()
    Dim wsIncoming As Worksheet
    Dim targetSheets As Variant
    Dim offsets As Variant
    Dim startRow As Long, numRows As Long
    Dim i As Integer
    Dim sourceRange As Range
    
    ' Define your target sheet names and corresponding row offsets
    targetSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6") ' Replace with actual sheet names
    offsets = Array(2, 1, 0, -1, -2, -3) ' Adjust offsets to match your needs (e.g., 2 for 57:61 when startRow=55)
    
    ' Set reference to incoming data sheet
    Set wsIncoming = ThisWorkbook.Sheets("Incoming")
    
    ' Grab user input values
    startRow = wsIncoming.Range("B1").Value
    numRows = wsIncoming.Range("B2").Value
    
    ' Loop through each target sheet and copy the correct row range
    For i = LBound(targetSheets) To UBound(targetSheets)
        Dim wsTarget As Worksheet
        Set wsTarget = ThisWorkbook.Sheets(targetSheets(i))
        
        ' Clear existing data in target sheet (adjust row range as needed)
        wsTarget.Rows("3:" & wsTarget.Rows.Count).ClearContents
        
        ' Calculate source row range with current offset
        Dim sourceStart As Long
        sourceStart = startRow + offsets(i)
        Set sourceRange = wsIncoming.Rows(sourceStart & ":" & sourceStart + numRows - 1)
        
        ' Copy and paste to target sheet
        sourceRange.Copy
        wsTarget.Rows("3").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Next i
    
    Application.CutCopyMode = False
    MsgBox "Row ranges copied to all target sheets successfully!"
End Sub

Key Improvements #

• Eliminated Select/Activate: Direct worksheet/range references make the macro faster and avoid issues with lost active cells.
• Explicit Object References: All ranges and worksheets are explicitly defined, preventing "object required" errors.
• Scalable Structure: Arrays for sheets and offsets let you easily adjust the number of targets or offsets without rewriting code.
• User Input Alignment: Uses B1 for start row and B2 for row count, matching your requirement for user-provided values.

内容的提问来源于stack exchange，提问作者JR Stroik