选中连续单元格执行操作时触发Type Mismatch Error问题求助

我把ChangeTypeByDoubleClick函数绑定到按钮「Change Type in Bulk」，通过调用Worksheet_BeforeDoubleClick宏实现双击选中单元格时循环切换值为"B&A"、"B"、"A"、""。但选中连续单元格时会触发Type Mismatch Error，错误出现在Worksheet_BeforeDoubleClick的这行代码：

ElseIf target.Row > Range(Print_Something_Rg).Row And Len(Trim(target.Offset(, 1).Value)) > 0 Then

单独选单个单元格时无此错误。

代码逻辑与变量说明 #

• Change Type in Bulk按钮调用ChangeTypeByDoubleClick，后者触发Worksheet_BeforeDoubleClick对选中单元格执行双击操作；
• Print_Something_Rg为定义的范围，指向"Type"右侧单元格，作为产品代码起始位置；
• target为选中的单元格区域。

代码示例 #

ChangeTypeByDoubleClick 代码

Sub ChangeTypeByDoubleClick()
    Call Sheet5.Worksheet_BeforeDoubleClick(Selection, False)
End Sub

Worksheet_BeforeDoubleClick 代码

Public Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    Dim rg_block    As Range
    
    If target.Column = Range(Print_Something_Rg).Offset(, -1).Column And Len(Trim(Range(Print_Something_Rg).Offset(1).Value)) > 0 Then
        Cancel = True
        If target.Row = Range(Print_Something_Rg).Row Then
            If Len(Trim(target.Offset(1, 1).Value)) > 0 Then
                Set rg_block = Range(target.Offset(1, 1), target.Offset(1, 1).End(xlDown)).Offset(, -1)
                If target.Offset(1).Value = "" Then
                    rg_block.Value = "B & A"
                ElseIf target.Offset(1).Value = "B & A" Then
                    rg_block.Value = "B"
                ElseIf target.Offset(1).Value = "B" Then
                    rg_block.Value = "A"
                Else
                    rg_block.Value = ""
                End If
            End If
        ElseIf target.Row > Range(Print_Something_Rg).Row And Len(Trim(target.Offset(, 1).Value)) > 0 Then
            If target.Value = "" Then
                target.Value = "B & A"
            ElseIf target.Value = "B & A" Then
                target.Value = "B"
            ElseIf target.Value = "B" Then
                target.Value = "A"
            Else
                target.Value = ""
            End If
        End If
    End If
End Sub

问题原因 #

虽然Selection是Range类型，但选中多个连续单元格时：

1. target.Offset(,1).Value会返回一个数组（因为Offset后的区域对应多个单元格），而Trim函数只能处理单个字符串，无法解析数组，直接触发类型不匹配错误；
2. 后续target.Value同样是数组，直接和字符串比较也会存在逻辑问题，但报错先出现在Len(Trim(...))这一步。

解决方案 #

修改代码逻辑，遍历target区域内的每个单元格逐个处理，避免直接对多单元格区域的Value属性做字符串操作：

Public Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    Dim rg_block    As Range
    Dim cell        As Range ' 用于遍历单个单元格
    
    If target.Column = Range(Print_Something_Rg).Offset(, -1).Column And Len(Trim(Range(Print_Something_Rg).Offset(1).Value)) > 0 Then
        Cancel = True
        If target.Row = Range(Print_Something_Rg).Row Then
            ' 表头行逻辑保持不变，此处target应为单个单元格
            If Len(Trim(target.Offset(1, 1).Value)) > 0 Then
                Set rg_block = Range(target.Offset(1, 1), target.Offset(1, 1).End(xlDown)).Offset(, -1)
                If target.Offset(1).Value = "" Then
                    rg_block.Value = "B & A"
                ElseIf target.Offset(1).Value = "B & A" Then
                    rg_block.Value = "B"
                ElseIf target.Offset(1).Value = "B" Then
                    rg_block.Value = "A"
                Else
                    rg_block.Value = ""
                End If
            End If
        ElseIf target.Row > Range(Print_Something_Rg).Row Then
            ' 遍历选中区域的每个单元格
            For Each cell In target
                ' 检查当前单元格右侧内容是否非空
                If Len(Trim(cell.Offset(, 1).Value)) > 0 Then
                    Select Case cell.Value
                        Case ""
                            cell.Value = "B & A"
                        Case "B & A"
                            cell.Value = "B"
                        Case "B"
                            cell.Value = "A"
                        Case Else
                            cell.Value = ""
                    End Select
                End If
            Next cell
        End If
    End If
End Sub

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