0

I have a small macro that checks all cells on a sheet to see if they have #DIV/0! Errors. I am trying to quickly replace all #DIV/0! messages in all cells with '=IFERROR(any_formula,0)' in each cell to handle all errors, and there are a lot of errors, so I want to use a macro to do the work.

Here is my macro:

Sub FindErrors()
Dim r as range
Dim sCell as Range
Worksheets("AllErrors").Select

Set r = Activesheet.UsedRange

   For each sCell in r
      If IsError(sCell.Value) Then 
         sCell.Select
         Activecell.FormulaR1C1 = "=IFERROR(" & Mid(sCell.Formula, 2, 999) & ",0)"
      End If
   Next sCell

End Sub

When the macro runs, I get results like this:

=IFERROR(@'P5'/@'O5',0)

So, the error is gone, but the @ and extra apostrophes look really weird. Is there a better/preferred way to do this kind of thing?

halfer
  • 19,824
  • 17
  • 99
  • 186
ASH
  • 20,759
  • 19
  • 87
  • 200
  • Use `.Formula2`. Also, note that `IsError` doesn't distinguish between `#DIV/0!` and any other type of error. – BigBen Mar 21 '22 at 16:00
  • Sooo....changing .Formula to .Formula2 does not eliminate all the @ characters and all the apostrophe characters. Am I missing something here? – ASH Mar 21 '22 at 16:28
  • 1
    Did you change `.FormulaR1C1` to `.Formula2` as well? – BigBen Mar 21 '22 at 16:29
  • 1
    I just did it! Now I see now it works! Thanks so much, for getting me pointed in the right direction!!! – ASH Mar 21 '22 at 16:37

0 Answers0