VBA单元格引用报错“对象变量未设置”，合并列功能失效求助

Let's break down the issues in your code and fix them step by step:

Key Issues Causing the Error #

1. Incorrect Variable Reference: You're using [taxableBox] (bracket notation) which refers to a worksheet-named range, not your VBA variable taxableBox. This is a critical mistake that leads to invalid range access.
2. Unchecked Find Results: If the Find method can't locate your header text ("Box number, Taxable Amount" or "Box number, VAT Amount"), it returns Nothing. Trying to call Offset on a Nothing range triggers the error you're seeing.
3. Undefined LastRow: Your loop uses LastRow but doesn't assign a value to it, which will cause unexpected behavior.
4. Unnecessary Select/ActiveCell: Relying on ActiveCell is fragile (it can change if the user interacts with Excel while the code runs) and slower than working directly with ranges.

Corrected Code #

Sub MergeTaxableAndVATBoxes()
    Dim taxableBox As Range
    Dim vatBox As Range
    Dim i As Integer
    Dim lastRow As Long
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    ' Set references to your worksheets (avoids typing the name repeatedly)
    Set wsSource = ThisWorkbook.Worksheets("VAT return layout")
    Set wsTarget = ThisWorkbook.Worksheets("Expected VAT return")
    
    ' Find the header columns with precise matching
    Set taxableBox = wsSource.Range("2:2").Find( _
        What:="Box number, Taxable Amount", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)
    
    Set vatBox = wsSource.Range("2:2").Find( _
        What:="Box number, VAT Amount", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)
    
    ' Check if both headers were found before proceeding
    If taxableBox Is Nothing Or vatBox Is Nothing Then
        MsgBox "One or both header columns not found!", vbExclamation
        Exit Sub
    End If
    
    ' Determine the last row with data in the source columns
    lastRow = Application.Max( _
        wsSource.Cells(wsSource.Rows.Count, taxableBox.Column).End(xlUp).Row, _
        wsSource.Cells(wsSource.Rows.Count, vatBox.Column).End(xlUp).Row)
    
    ' Loop through each row and populate the target range (B4 onwards)
    For i = 1 To lastRow - 2 ' Offset from header row (row 2) to data rows
        Dim taxableValue As Variant
        Dim vatValue As Variant
        
        taxableValue = wsSource.Cells(taxableBox.Row + i, taxableBox.Column).Value
        vatValue = wsSource.Cells(vatBox.Row + i, vatBox.Column).Value
        
        Select Case True
            Case Not IsEmpty(taxableValue) And Not IsEmpty(vatValue)
                wsTarget.Cells(3 + i, 2).Value = taxableValue & "/" & vatValue ' Start at row 4 (B4)
            Case Not IsEmpty(taxableValue)
                wsTarget.Cells(3 + i, 2).Value = taxableValue
            Case Not IsEmpty(vatValue)
                wsTarget.Cells(3 + i, 2).Value = vatValue
            Case Else
                wsTarget.Cells(3 + i, 2).Value = ""
        End Select
    Next i
End Sub

Explanation of Fixes #

• Worksheet References: Assigning wsSource and wsTarget makes the code cleaner and reduces errors if worksheet names change.
• Improved Find Method: Added LookIn:=xlValues and LookAt:=xlWhole to ensure we match the exact header text, avoiding partial matches.
• Error Checking: We validate that both headers were found before proceeding, preventing the "Object variable not set" error.
• Proper LastRow Calculation: We find the last row with data in either source column to avoid processing empty rows unnecessarily.
• Direct Range Access: Instead of relying on Select and ActiveCell, we write directly to the target cells, making the code faster and more reliable.
• Robust Empty Check: Using IsEmpty handles both blank cells and cells with formulas that return empty strings, which is more reliable than checking for "".

Why Your Original Code Worked Before #

It’s likely that previously, the Find method always successfully located both headers, so taxableBox and vatBox were valid ranges. If the header text changed slightly (e.g., extra space, case difference) or the worksheet structure was modified, Find started returning Nothing, triggering the error. The incorrect [taxableBox] reference might have been masked earlier if there was a named range with the same name, but that’s not a reliable setup.

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