VBA runtime error 13 type mismatch when executing for loop to replace cell value [duplicate]

Question

I have the following code in VBA:

Sub creditformat()
Dim ws As Worksheet
Dim lastrow As Long

`
Set ws = ThisWorkbook.Worksheets("Table1")

lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row

For i = 1 To lastrow
**If ws.Cells(i, "AC").Value = "N.A" Then**
        ws.Cells(i, "AC").Value = "Unrated"
    End If
    
Next i
End Sub`

The line between two asterisks is the one causing issues. Does anyone know how to resolve this? I thought it should be a fairly simply script, but somehow it doesn't work. Any help would be appreciated, thanks!

I've also tried defining a range, like

Set rng = ws.Range("AC2:AC" & lastrow)

For Each cell In rng
    If cell.Value = "N.A" Then
        cell.Value = "Unrated"
    End If
    
Next cell

But it doesn't work as well

If the error is a type mismatch then it probably means just that: there's a mismatch between the value you are checking and the "N.A". What does the workbook look like? — Taren Sanders
– Taren Sanders, Commented Jul 7, 2023 at 2:47
The cells aren't formatted so it's just in the General excel format. It's populated with various credit ratings like "AAA or equivalent" etc. The thing is I tried it in another workbook and it works, but not in this so it's quite strange — Gerald Ho
– Gerald Ho, Commented Jul 7, 2023 at 3:02
Try If IsError(cell.Value) Then because "N.A" is only the visibility of the error type. — Black cat
– Black cat, Commented Jul 7, 2023 at 4:12
Hey, actually it's not an excel formula error the cell's value is N.A haha. — Gerald Ho
– Gerald Ho, Commented Jul 7, 2023 at 8:00

andrewb · Accepted Answer · 2023-07-07 06:14:41Z

0

There are two possible scenarios here in my opinion:

1. You are trying to check whether the ws.Cells(i, "AC").Value is an error.

In this case, Black cat's suggestion is correct, you must use IsError like so:

Sub creditformat()
Dim ws As Worksheet
Dim lastrow As Long


Set ws = ThisWorkbook.Worksheets("Table1")

lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row

For i = 1 To lastrow
    If IsError(ws.Cells(i, "AC").Value) Then
        ws.Cells(i, "AC").Value = "Unrated"
    End If
Next i
End Sub

2. You are actually attempting to compare against the "N.A" string but some of the cells in your range have errors in them (which you don't care about), which causes the type mismatch. In this case, some error handling is needed. Try this:

Sub creditformat()
Dim ws As Worksheet
Dim lastrow As Long


Set ws = ThisWorkbook.Worksheets("Table1")

lastrow = ws.Cells(ws.Rows.Count, "AC").End(xlUp).Row

For i = 1 To lastrow
    On Error GoTo skip
        If ws.Cells(i, "AC").Value = "N.A" Then
            ws.Cells(i, "AC").Value = "Unrated"
        End If
skip:
    On Error GoTo -1
Next i
End Sub

answered Jul 7, 2023 at 6:14

andrewb

1,1967 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerald Ho Over a year ago

Thanks this is it! I didn't realise errors in cells would cause the macro to break.

FunThomas · Accepted Answer · 2023-07-07 07:43:33Z

I'd suggest breaking out the code that causes the error into a separate procedure, so, add this to the same Module that your creditformat procedure is in (or another standard Module):

Function IsCellNA(cel As Range) As Boolean
    On Error Resume Next
    IsCellNA = cel.Value = "N.A"
End Function

... this will return True if the cell contains a text value of "N.A" and False for any other value but the key things here are:

It still returns False if the test of the value results in an error (which it could be if the cell itself contains an error code)
If the test results in an error, that error is suppressed within this procedure and the calling (ie the creditformat procedure) code can continue running to conclusion

Then adjust the If ws.Cells(i, "AC").Value = "N.A" Then line to call this newly-added function, eg If IsCellNA(ws.Cells(i, "AC")) Then

Collectives™ on Stack Overflow

VBA runtime error 13 type mismatch when executing for loop to replace cell value [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related