运行VBA代码时出现Run-time error 13类型不匹配问题求助

你遇到的Run-time error 13（类型不匹配），根源出在变量声明不规范和未校验单元格值有效性两个方面，以下是具体排查和修复方案：

问题根源分析 #

1. 变量声明错误：VBA中，Dim course, recette, dossier, fichier, filePath As String 写法仅会把filePath声明为String类型，其余course、recette、dossier、fichier都是默认的Variant类型。如果对应单元格里是错误值（比如#N/A、#VALUE!），Variant会直接存储错误值，后续拼接字符串时，错误值与字符串无法兼容，触发类型不匹配。
2. 未校验单元格值：直接读取tableRow.Columns(1).Value赋值给变量，未判断是否为错误值或空值，一旦存在无效值，拼接路径时必然报错。

修复步骤 #

1. 修正变量声明 #

把所有需要存储字符串的变量都明确声明为String类型：

Dim tableRow As Range
Dim course As String, recette As String, dossier As String, fichier As String, filePath As String
Dim locArray As Variant, dynCol As Variant
Private Const RECAP = "Récap"
Private Const RESULTAT = "Résultats"

2. 增加单元格值有效性校验 #

在拼接filePath之前，先检查course、dossier、fichier是否为有效字符串，跳过错误值或空值的行：

For Each tableRow In [Table1].Rows
    If tableRow.Row <> 3 Then
        ' 先判断单元格是否为错误值，再赋值
        If Not IsError(tableRow.Columns(1).Value) And Not IsError(tableRow.Columns(3).Value) And Not IsError(tableRow.Columns(4).Value) Then
            course = CStr(tableRow.Columns(1).Value)
            dossier = CStr(tableRow.Columns(3).Value)
            fichier = CStr(tableRow.Columns(4).Value)
            
            ' 校验关键字段非空
            If course <> "" And dossier <> "" And fichier <> "" Then
                filePath = "Y:\Production - opérations\meunerie\MEUNIER\Résultats Tests de Moutures " & _
                            dossier & _
                            "\[" & course & "-" & fichier & ".xlsm]"
                
                i = 5
                For Each dynCol In locArray
                    If Not IsError(tableRow.Columns(i)) Then
                        If tableRow.Columns(i) = "" Then
                            FillCol tableRow.Columns(i), filePath, dynCol(0), dynCol(1)
                        End If
                    End If
                    i = i + 1
                Next
            End If
        End If
    End If
Next

3. 可选：增加文件存在性校验 #

为避免后续引用不存在的文件，可以在拼接路径后，用Dir函数检查文件是否存在：

' 拼接filePath后添加
Dim fullPath As String
fullPath = "Y:\Production - opérations\meunerie\MEUNIER\Résultats Tests de Moutures " & dossier & "\" & course & "-" & fichier & ".xlsm"
If Dir(fullPath) = "" Then
    MsgBox "文件不存在：" & fullPath, vbExclamation
    Exit For
End If

修改后的完整代码 #

Dim tableRow As Range
Dim course As String, recette As String, dossier As String, fichier As String, filePath As String
Dim locArray As Variant, dynCol As Variant

Private Const RECAP = "Récap"
Private Const RESULTAT = "Résultats"

Sub AutoFill()
    locArray = Array( _
        Array(RESULTAT, "$S$1"), Array(RESULTAT, "$O$1"), Array(RESULTAT, "$X$1"), Array(RECAP, "$V$12"), Array(RECAP, "$W$12"), _
        Array(RECAP, "$X$12"), Array(RECAP, "$Y$12"), Array(RECAP, "$V$17"), Array(RECAP, "$W$17"), Array(RECAP, "$X$17"), _
        Array(RECAP, "$Y$17"), Array(RECAP, "$V$22"), Array(RECAP, "$W$22"), Array(RECAP, "$X$22"), Array(RECAP, "$Y$22"), _
        Array(RECAP, "$V$27"), Array(RECAP, "$W$27"), Array(RECAP, "$X$27"), Array(RECAP, "$Y$27"), Array(RECAP, "$V$32"), _
        Array(RECAP, "$W$32"), Array(RECAP, "$X$32"), Array(RECAP, "$Y$32"), Array(RECAP, "$V$37"), Array(RECAP, "$W$37"), _
        Array(RECAP, "$X$37"), Array(RECAP, "$Y$37"), Array(RECAP, "$V$42"), Array(RECAP, "$W$42"), Array(RECAP, "$X$42"), _
        Array(RECAP, "$Y$42"), Array(RECAP, "$AA$12"), Array(RECAP, "$AB$12"), Array(RECAP, "$AC$12"), Array(RECAP, "$AD$12"), _
        Array(RECAP, "$AA$22"), Array(RECAP, "$AB$22"), Array(RECAP, "$AC$22"), Array(RECAP, "$AD$22"), Array(RECAP, "$AA$27"), _
        Array(RECAP, "$AB$27"), Array(RECAP, "$AC$27"), Array(RECAP, "$AD$27"), Array(RECAP, "$AA$32"), Array(RECAP, "$AB$32"), _
        Array(RECAP, "$AC$32"), Array(RECAP, "$AD$32"), Array(RECAP, "$AA$42"), Array(RECAP, "$AB$42"), Array(RECAP, "$AC$42"), _
        Array(RECAP, "$AD$42"), Array(RECAP, "$AF$12"), Array(RECAP, "$AG$12"), Array(RECAP, "$AH$12"), Array(RECAP, "$AI$12"), _
        Array(RECAP, "$AF$17"), Array(RECAP, "$AG$17"), Array(RECAP, "$AH$17"), Array(RECAP, "$AI$17"), Array(RECAP, "$AF$22"), _
        Array(RECAP, "$AG$22"), Array(RECAP, "$AH$22"), Array(RECAP, "$AI$22"), Array(RECAP, "$AF$27"), Array(RECAP, "$AG$27"), _
        Array(RECAP, "$AH$27"), Array(RECAP, "$AI$27"), Array(RECAP, "$AI$52"), Array(RECAP, "$AI$54"), Array(RESULTAT, "$K$1"), _
        Array(RECAP, "$W$54"), Array(RECAP, "$X$54"), Array(RECAP, "$Y$54"), Array(RECAP, "$Z$54"), Array(RECAP, "$AA$54"), _
        Array(RECAP, "$AB$54"), Array(RECAP, "$AC$54"), Array(RECAP, "$W$56"), Array(RECAP, "$X$56"), Array(RECAP, "$Y$56"), _
        Array(RECAP, "$Z$56"), Array(RECAP, "$AA$56"), Array(RECAP, "$AB$56"), Array(RECAP, "$AC$56"), Array(RECAP, "$AD$55/1000"), _
        Array(RECAP, "$AF$32"), Array(RECAP, "$AG$32"), Array(RECAP, "$AH$32"), Array(RECAP, "$AI$32"), Array(RECAP, "$AH$42"), _
        Array(RECAP, "$AH$45"), Array(RECAP, "$AH$46"), Array(RECAP, "$AH$47"), Array(RECAP, "$AH$48") _
    )
    
    For Each tableRow In [Table1].Rows
        If tableRow.Row <> 3 Then
            ' 校验单元格是否为错误值
            If Not IsError(tableRow.Columns(1).Value) And _
               Not IsError(tableRow.Columns(3).Value) And _
               Not IsError(tableRow.Columns(4).Value) Then
               
                course = CStr(tableRow.Columns(1).Value)
                dossier = CStr(tableRow.Columns(3).Value)
                fichier = CStr(tableRow.Columns(4).Value)
                
                ' 校验关键字段非空
                If course <> "" And dossier <> "" And fichier <> "" Then
                    filePath = "Y:\Production - opérations\meunerie\MEUNIER\Résultats Tests de Moutures " & _
                                dossier & _
                                "\[" & course & "-" & fichier & ".xlsm]"
                    
                    ' 可选：检查文件是否存在
                    Dim fullPath As String
                    fullPath = "Y:\Production - opérations\meunerie\MEUNIER\Résultats Tests de Moutures " & dossier & "\" & course & "-" & fichier & ".xlsm"
                    If Dir(fullPath) = "" Then
                        MsgBox "跳过不存在的文件：" & fullPath, vbExclamation
                        GoTo NextRow
                    End If
                    
                    i = 5
                    For Each dynCol In locArray
                        If Not IsError(tableRow.Columns(i)) Then
                            If tableRow.Columns(i) = "" Then
                                FillCol tableRow.Columns(i), filePath, dynCol(0), dynCol(1)
                            End If
                        End If
                        i = i + 1
                    Next
                End If
            End If
NextRow:
        End If
    Next
End Sub

Sub FillCol(col As Range, filePath As String, sheet As String, data As String)
    col.Value = "='" & filePath & sheet & "'!" & data
End Sub

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