VBA中InputBox设Type=2/1+2输入字符串触发Type Mismatch错误

输入“Table5”后触发运行时错误'13'：类型不匹配，错误出现在以下代码段：

TableName = Application.InputBox( _
    prompt:="Enter the target table name", _
    Title:="Table Name", _
    Type:=1 + 2, _
    Default:="Table1")
On Error GoTo 0

我尝试将Type参数设置为"2"和"1 + 2"，但问题依旧。

宏功能说明 #

该宏用于在指定区域填充=COUNTIF(TableName[ColumnName], FALSE)格式的公式，参数定义：

• TargetRange：待填充的目标区域（第一个输入变量）
• ColumnName：包含表格列名的区域，范围与TargetRange一致（第二个输入变量）
• TableName：包含目标列和需统计FALSE值的表格名称

预期效果 #

目标区域的每个单元格生成对应列的FALSE值计数公式，自动统计指定表格中对应列的FALSE数量。

完整原始代码 #

Dim TableName, TargetRange As Range, ColumnName As Range
'Input variables'
Set TargetRange = Application.InputBox( _
    prompt:="Select the output range where result will be pasted", _
    Title:="Output Range", _
    Type:=8)
On Error GoTo 0
If TargetRange Is Nothing Then
    Exit Sub
End If
Set ColumnName = Application.InputBox( _
    prompt:="Select range containing the column name", _
    Title:="Column name", _
    Type:=8)
On Error GoTo 0
If ColumnName Is Nothing Then
    Exit Sub
End If
TableName = Application.InputBox( _
    prompt:="Enter the name of the table", _
    Title:="Table Name", _
    Type:=1 + 2, _
    Default:="Table1")
On Error GoTo 0
'For Loop'
Dim TargetCell As Range
Dim Name
For Each TargetCell In TargetRange
    For Each Name In ColumnName
        Range(TargetCell).Value = "=COUNTIF(" & TableName & "[" & ColumnName & "], FALSE)"
        On Error Resume Next
        Next Name
    Next TargetCell
End Sub

1. 修正变量声明与输入框参数 #

• TableName未明确类型，默认是Variant，需声明为String；
• InputBox的Type参数不需要加引号，仅需文本输入时设为2即可。

修改后的变量声明与输入框代码：

Dim TableName As String, TargetRange As Range, ColumnName As Range

TableName = Application.InputBox( _
    prompt:="Enter the name of the table", _
    Title:="Table Name", _
    Type:=2, _
    Default:="Table1")
On Error GoTo 0
If TableName = "" Then
    Exit Sub
End If

2. 修复循环逻辑与公式拼接 #

• 原嵌套循环逻辑错误，TargetRange与ColumnName是一一对应关系，用索引遍历更合理；
• 公式拼接时需取ColumnName中每个单元格的文本值，而非直接引用Range对象；
• TargetCell已是Range对象，无需再用Range(TargetCell)。

修改后的循环代码：

'For Loop'
Dim i As Integer
'校验两个区域单元格数量一致'
If TargetRange.Cells.Count <> ColumnName.Cells.Count Then
    MsgBox "目标区域和列名区域的单元格数量必须一致！"
    Exit Sub
End If

For i = 1 To TargetRange.Cells.Count
    TargetRange.Cells(i).Formula = "=COUNTIF(" & TableName & "[" & ColumnName.Cells(i).Value & "], FALSE)"
Next i

3. 完整修复后代码 #

Sub CountFalseInTable()
    Dim TableName As String, TargetRange As Range, ColumnName As Range
    
    '选择输出区域'
    Set TargetRange = Application.InputBox( _
        prompt:="Select the output range where result will be pasted", _
        Title:="Output Range", _
        Type:=8)
    On Error GoTo 0
    If TargetRange Is Nothing Then
        Exit Sub
    End If
    
    '选择列名区域'
    Set ColumnName = Application.InputBox( _
        prompt:="Select range containing the column name", _
        Title:="Column name", _
        Type:=8)
    On Error GoTo 0
    If ColumnName Is Nothing Then
        Exit Sub
    End If
    
    '输入表格名称'
    TableName = Application.InputBox( _
        prompt:="Enter the name of the table", _
        Title:="Table Name", _
        Type:=2, _
        Default:="Table1")
    On Error GoTo 0
    If TableName = "" Then
        Exit Sub
    End If
    
    '填充公式'
    Dim i As Integer
    If TargetRange.Cells.Count <> ColumnName.Cells.Count Then
        MsgBox "Target range and column name range must have the same number of cells!"
        Exit Sub
    End If
    
    For i = 1 To TargetRange.Cells.Count
        TargetRange.Cells(i).Formula = "=COUNTIF(" & TableName & "[" & ColumnName.Cells(i).Value & "], FALSE)"
    Next i
End Sub

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