VBA运行时错误'424'：Range变量ChoiceArr提示对象必需

核心错误原因 #

Application.Transpose 处理单元格区域后返回的是Variant数组，而非Range对象。原代码用Set（仅用于对象赋值）给Range类型变量赋值数组，必然触发"对象必需"错误；改成Variant后仍有问题，是因为还存在工作表引用不明确、Match无错误处理等其他隐患。

修正方案分两种场景 #

场景1：需要获取"Choice"列的单元格区域（而非数组）

如果只是要引用目标列的单元格范围，无需使用Transpose，直接修正工作表引用并处理Match错误即可：

Sub wasp1()
    Dim Head1 As Range
    Dim now1 As Long ' 替换Integer，避免行数超过32767时溢出
    Dim ChoiceArr As Range
    Dim choiceCol As Long
    
    ' 用With块锁定目标工作表，避免引用混乱
    With Worksheets(1)
        now1 = .Range("A1").CurrentRegion.Rows.Count
        ' 所有Range前加.，确保引用当前With指定的工作表
        Set Head1 = .Range(.Range("A1"), .Range("A1").End(xlToRight))
        
        ' 处理Match找不到"Choice"的情况
        On Error Resume Next
        choiceCol = WorksheetFunction.Match("Choice", Head1, 0)
        On Error GoTo 0
        
        If choiceCol > 0 Then
            ' 直接引用第2行到末行的目标列区域
            Set ChoiceArr = .Cells(2, choiceCol).Resize(now1 - 1, 1)
        Else
            MsgBox "未找到名为Choice的列"
            Exit Sub
        End If
    End With
End Sub

场景2：需要获取转置后的一维数组（如用于下拉列表）

此时需将ChoiceArr声明为Variant，直接赋值数组（无需Set）：

Sub wasp1()
    Dim Head1 As Range
    Dim now1 As Long
    Dim ChoiceArr As Variant
    Dim choiceCol As Long
    
    With Worksheets(1)
        now1 = .Range("A1").CurrentRegion.Rows.Count
        Set Head1 = .Range(.Range("A1"), .Range("A1").End(xlToRight))
        
        On Error Resume Next
        choiceCol = WorksheetFunction.Match("Choice", Head1, 0)
        On Error GoTo 0
        
        If choiceCol > 0 Then
            ' 直接赋值转置后的数组，不用Set
            ChoiceArr = Application.Transpose(.Cells(2, choiceCol).Resize(now1 - 1, 1).Value)
        Else
            MsgBox "未找到名为Choice的列"
            Exit Sub
        End If
    End With
    
    ' 可选：验证数组内容（输出到立即窗口）
    Dim i As Long
    For i = LBound(ChoiceArr) To UBound(ChoiceArr)
        Debug.Print ChoiceArr(i)
    Next i
End Sub

关键修改点 #

• 用With Worksheets(1)明确所有单元格引用的工作表，避免活动表切换导致的错误。
• 替换Integer为Long，适配Excel最大行数（超过Integer的32767上限）。
• 增加Match函数的错误处理，防止找不到目标列时程序崩溃。
• 区分对象（Range）和非对象（数组）的赋值逻辑：对象用Set，数组直接赋值。

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