Excel VBA运行报错Type Mismatch：sName=arBanker(n,1)行问题排查

核心报错原因 #

sName = arBanker(n, 1) 触发类型不匹配通常为以下3种情况：

• Banker工作表A列仅A1表头有值，A2及以下无有效数据：此时iLastRow计算结果为1，Range("A2:A1")为无效区域，赋值后的arBanker不是二维数组，无法用二维索引取值
• Banker工作表A2到最后一行中存在单元格错误值：例如#N/A、#VALUE!等错误值无法直接赋值给字符串类型的sName
• 仅存在1个有效Banker值：此时单单元格赋值给arBanker后，变量为单个值而非二维数组，用二维索引取值也会触发类型错误

修复后完整代码 #

Option Explicit
Sub separate_by_banker()
    Dim wb As Workbook, wbNew As Workbook
    Dim ws As Worksheet, wsData As Worksheet
    Dim rng As Range, arBanker As Variant
    Dim n As Long, iLastRow As Long, validCount As Long
    Dim foldername As String, sName As String
    Const ILLEGAL_CHARS As String = "\/:*?""<>|"
    Dim c As Integer
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Banker")
    iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 校验Banker列表非空
    If iLastRow < 2 Then
        MsgBox "Banker表A列无有效数据，程序退出", vbExclamation
        Exit Sub
    End If
    arBanker = ws.Range("A2:A" & iLastRow).Value2
    
    ' 处理单值场景（非数组）
    If Not IsArray(arBanker) Then
        ReDim arBanker(1 To 1, 1 To 1)
        arBanker(1, 1) = ws.Range("A2").Value2
    End If
    
    Set wsData = wb.Sheets("Asia (PC)")
    iLastRow = wsData.Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = wsData.Range("A1:Y" & iLastRow)
    
    ' 创建存储文件夹
    foldername = wb.Path & "\" & wb.Name & " " & Format(Now, "yyyy-mm-dd hh-mm-ss")
    MkDir foldername
    
    Application.ScreenUpdating = False
    validCount = 0
    For n = 1 To UBound(arBanker)
        ' 跳过错误值和空值
        If Not IsError(arBanker(n, 1)) And Trim(arBanker(n, 1)) <> "" Then
            sName = CStr(arBanker(n, 1))
            ' 替换文件名/工作表名非法字符
            For c = 1 To Len(ILLEGAL_CHARS)
                sName = Replace(sName, Mid(ILLEGAL_CHARS, c, 1), "_")
            Next
            
            Set wbNew = Workbooks.Add(xlWBATWorksheet)
            wbNew.Sheets(1).Name = Left(sName, 30) ' 工作表名最多31字符
            rng.AutoFilter Field:=7, Criteria1:=CStr(arBanker(n, 1))
            rng.Copy
            wbNew.Sheets(1).Paste
            wbNew.SaveAs foldername & "\" & sName & ".xlsx"
            wbNew.Close False
            validCount = validCount + 1
        End If
    Next
    wsData.AutoFilterMode = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    MsgBox validCount & " 个文件已生成，存储路径：" & foldername, vbInformation
End Sub

额外优化说明 #

• 新增了非法字符替换逻辑，避免Banker名称包含特殊字符导致保存文件、重命名工作表报错
• 新增有效计数逻辑，最终提示的生成文件数为实际有效生成的数量，排除空值、错误值的干扰
• 限制工作表名长度不超过30个字符，避免超出Excel工作表名长度限制报错

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