VBA Inserts "NM" instead of 0""

[prescient]

So I macro that should grab the existing cell formula and then wrap it with an iferror, but instead of inserting a 0 into the iferror formula it inserts "NM".

Sub InsertIfError()
'
' InsertIfError Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim c As Range
    
    For Each c In Selection
        c.forumla = "=iferror(" & c.Formula & ", 0 )"
    Next c
    
End Sub

For example if the cell formula is "=A1+B1" and I run the macro it will insert "=iferror(A1+B1,"NM")"

[dangelor]

Try

Sub InsertIfError()
'
' InsertIfError Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
    Dim c           As Range
    Dim f           As String

    For Each c In Selection
        f = Mid(c.Formula, 2)
        c.Formula = "=iferror(" & f & ", 0 )"
    Next c

End Sub

[TMS]

Try:

Sub InsertIfError()
'
' InsertIfError Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim c As Range
    
For Each c In Selection
    c.Formula = _
        "=iferror(" & Right(c.Formula, Len(c.Formula) - 1) & ", 0 )"
Next c
    
End Sub

Regards, TMS

[prescient]

Weird they all insert that "NM" instead. I'm clueless.

I even tried:

Dim c As Range
Dim f As String

    
For Each c In Selection
    f = "Iferror(" & c.Formula & ", 0 )"
    c.Formula = f
Next c
    
End Sub

[TMS]

Please post a sample workbook.

[OnErrorGoto0]

Does this happen with all workbooks/worksheets?

[JapanDave]

I don't know if this is what you are after, but try this.

Sub InsertIfError()
'
' InsertIfError Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Dim c As Range
Dim rng As Range
Dim i As String

    Set rng = Range("A1:A22")
    
        For Each c In rng
           i = c.Formula
           i = Right(i, Len(i) - 1)
           c = "=IFERROR(" & i & " , ""0"")"
           
        Next c
    
End Sub

[prescient]

OK so If I run the macro with the hotkey setup it inserts the "NM" but if I manually launch the sub it works fine.

[TMS]

Both of these routines work when run manually, the first by selecting a range initially.

I see no reason why they would work differently from a short cut key. Unless you have changed which macro the short cut key links to, you may well be running your original code.

The second one definitely works eith Ctrl-Shift-E

Sub InsertIfError_TMS_Select()
'
Dim c As Range
    
For Each c In Selection
    c.Formula = _
        "=iferror(" & Right(c.Formula, Len(c.Formula) - 1) & ", 0 )"
Next c
    
End Sub

Sub InsertIfError_TMS_Range()
'
Dim c As Range
    
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    c.Formula = _
        "=iferror(" & Right(c.Formula, Len(c.Formula) - 1) & ", 0 )"
Next c
    
End Sub

[TMS]

Forgot to mention; the only time NM appears is in the existing formula.

Regards, TMS

[OnErrorGoto0]

The macro in the workbook does not appear to be assigned to a shortcut key.