Excel对比输入VBA宏出现Error 400错误，请求协助

问题根源分析 #

你的代码触发Error 400主要有三个原因：

• 变量j未显式声明：VBA隐式声明变量可能导致类型冲突，尤其当代码逻辑复杂时容易出问题。
• 空字符串处理逻辑错误：如果某列所有数据都不低于平均值，belowAvg会是空字符串，此时Len(belowAvg)-1等于-1，调用Left函数会直接报错。同理aboveAvg也存在这个问题。
• 平均值依赖外部单元格：如果Cells(30,i)是空值或非数值类型，赋值给Double类型的avg会引发类型不匹配错误，进而触发Error 400。

修复后的代码 #

Sub CompareAndInput()
    Dim avg As Double
    Dim belowAvg As String
    Dim aboveAvg As String
    Dim i As Integer
    Dim j As Integer ' 显式声明变量j
    
    For i = 2 To 44 ' B列到AR列（列号2-44）
        ' 直接计算当前列2-25行的平均值，替代依赖外部单元格
        avg = Application.WorksheetFunction.Average(Range(Cells(2, i), Cells(25, i)))
        
        belowAvg = ""
        aboveAvg = ""
        
        ' 遍历2-25行数据
        For j = 2 To 25
            ' 处理单元格为空的情况，避免空值参与比较
            If Not IsEmpty(Cells(j, i)) And IsNumeric(Cells(j, i)) Then
                If Cells(j, i) < avg Then
                    belowAvg = belowAvg & Cells(j, 1) & ","
                Else
                    aboveAvg = aboveAvg & Cells(j, 1) & ","
                End If
            End If
        Next j
        
        ' 移除末尾逗号前先判断字符串是否为空
        If Len(belowAvg) > 0 Then
            belowAvg = Left(belowAvg, Len(belowAvg) - 1)
        End If
        If Len(aboveAvg) > 0 Then
            aboveAvg = Left(aboveAvg, Len(aboveAvg) - 1)
        End If
        
        ' 写入结果到27、28行
        Cells(27, i) = belowAvg
        Cells(28, i) = aboveAvg
    Next i
End Sub

关键修改说明 #

1. 显式声明变量j：避免隐式声明带来的类型不确定性。
2. 直接计算平均值：用WorksheetFunction.Average直接计算目标区域的平均值，不再依赖第30行的单元格，避免外部数据异常导致的错误。
3. 增加空值/非数值判断：遍历单元格时先检查是否为空或非数值，避免空值参与比较引发的错误。
4. 安全处理末尾逗号：只有当字符串长度大于0时才移除末尾逗号，避免空字符串调用Left函数报错。

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