Excel VBA 使用列号而非列字母引用单元格区域时报错如何解决

问题根源 #

• 核心报错原因：Match函数第二个参数中的Cells(2,6)和Cells(2,LastCol)未指定所属工作表，VBA中无前缀的Cells默认指向当前激活的工作表，如果当前激活的不是Sheet3，就会出现跨工作表构造Range的1004错误。你之前尝试给Cells前加.无效，是因为.Cells必须包裹在With Sheet3代码块内才会绑定对应工作表，无With块时单独写.Cells本身属于语法错误。
• 附加隐患：如果Match未找到和orgname匹配的值，会直接抛出运行时错误，需要增加容错处理。
• 变量声明不规范：Dim LastCol, LastRow As Integer的写法中，LastCol会被默认定义为Variant类型而非整数，且Integer类型在Excel中容易溢出，推荐统一使用Long类型。

修复后完整代码 #

Public Sub cmb_orgname_Change()

Dim wb As Workbook
Dim sht As Worksheet
Dim orgname As String
Dim org_position As Variant ' 改用Variant接收匹配结果，方便判断是否匹配成功
Dim LastCol As Long, LastRow As Long ' 统一声明为Long类型，避免溢出和类型异常
Dim prod_range As Range
Dim cell As Range
Set sht = Sheet1

' 从下拉框获取选中的机构名称值
orgname = sht.OLEObjects("cmb_orgname").Object.Value

' 查找机构名称所在行的最后一列，当前逻辑暂未用到该值
LastCol = Sheet3.Range("F2").End(xlToRight).Column

If orgname <> "" Then
    Call Clear_ComboBox
    ' 给所有Cells添加Sheet3前缀，保证Range范围属于同一张工作表，同时增加匹配容错
    org_position = WorksheetFunction.IfError(WorksheetFunction.Match(orgname, Sheet3.Range(Sheet3.Cells(2, 6), Sheet3.Cells(2, LastCol)), 0), 0)
    ' 匹配不到直接退出，避免后续报错
    If org_position = 0 Then
        ' 可按需添加匹配失败提示，例如 MsgBox "未找到对应机构名称"
        Exit Sub
    End If
    org_position = org_position + 6
    LastRow = Sheet3.Cells(Sheet3.Rows.Count, org_position).End(xlUp).Row
    Set prod_range = Sheet3.Range(Sheet3.Cells(3, org_position), Sheet3.Cells(LastRow, org_position))
    
    For Each cell In prod_range
        With sht.OLEObjects("cmb_prodname").Object
            Dim test As String
            test = CStr(cell.Value)
            .AddItem CStr(cell.Value)
        End With
    Next cell
    
End If

End Sub

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