执行For循环替换单元格值时出现VBA运行时错误13（类型不匹配）

错误原因 #

AC列中存在非字符串类型的数据（比如错误值#N/A、数字、布尔值等），直接用cell.Value = "N.A"进行比较时，不同数据类型无法直接判断相等性，触发运行时错误13（类型不匹配）。

解决方案 #

方案1：先判断数据类型再比较

通过VarType确认单元格为字符串类型后，再执行比较替换：

Sub creditformat()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim i As Long ' 必须声明变量类型，避免变体类型隐患
    
    Set ws = ThisWorkbook.Worksheets("Table1")
    lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row
    
    For i = 1 To lastrow
        With ws.Cells(i, "AC")
            ' 仅处理字符串类型的单元格
            If VarType(.Value) = vbString Then
                If .Value = "N.A" Then .Value = "Unrated"
            End If
        End With
    Next i
End Sub

方案2：处理错误值+强制类型转换

先用IsError排除错误值单元格，再用CStr强制转换为字符串后比较：

Sub creditformat()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastrow As Long
    
    Set ws = ThisWorkbook.Worksheets("Table1")
    lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row
    Set rng = ws.Range("AC1:AC" & lastrow)
    
    For Each cell In rng
        ' 跳过错误值单元格
        If Not IsError(cell.Value) Then
            ' 强制转换为字符串后比较
            If CStr(cell.Value) = "N.A" Then
                cell.Value = "Unrated"
            End If
        End If
    Next cell
End Sub

方案3：使用Excel内置Replace方法（最优解）

直接调用Range的Replace方法，内部自动处理数据类型问题，且效率远高于循环单元格：

Sub creditformat()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim rng As Range
    
    Set ws = ThisWorkbook.Worksheets("Table1")
    lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row
    Set rng = ws.Range("AC1:AC" & lastrow)
    
    ' LookAt:=xlWhole 确保匹配整个单元格内容，避免部分匹配
    rng.Replace What:="N.A", Replacement:="Unrated", LookAt:=xlWhole, MatchCase:=False
End Sub

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