VBA for Iferror with pop up and open field

[ChrisM88]

Hi,
I used to have a piece of code which allowed me to select a series of cells and upon running the macro, it would update each formula with an IFERROR prefix, with a popup box to confirm what to return if the cell was in error, so for example, i could put 0 or "n/a".
Does anyone have something which does this job?

Thanks in advance

 

[Joe4]

Welcome to the Board!

Maybe something like this:

Sub CreateIFERRORFormulas()

    Dim cell As Range
    Dim ret As String
    
    Application.ScreenUpdating = False
    
'   Prompt user for what to return
    ret = InputBox("What would you like to return in case of an error?")
    
'   Loop through all cells in selection
    For Each cell In Selection
'       Turn formula into IFERROR formula
        If Left(cell.Formula, 1) = "=" Then
            cell.Formula = "=IFERROR(" & Mid(cell.Formula, 2, Len(cell.Formula)) & "," & Chr(34) & ret & Chr(34) & ")"
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[ChrisM88]

Welcome to the Board!

Maybe something like this:

Sub CreateIFERRORFormulas()

    Dim cell As Range
    Dim ret As String
   
    Application.ScreenUpdating = False
   
'   Prompt user for what to return
    ret = InputBox("What would you like to return in case of an error?")
   
'   Loop through all cells in selection
    For Each cell In Selection
'       Turn formula into IFERROR formula
        If Left(cell.Formula, 1) = "=" Then
            cell.Formula = "=IFERROR(" & Mid(cell.Formula, 2, Len(cell.Formula)) & "," & Chr(34) & ret & Chr(34) & ")"
        End If
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub

Perfect, really appreciate the response!!!

 

[Joe4]

You are welcome.
Glad I was able to help!