View original (French)

Replace cells containing #DIV/0!

aaa -  
Hello,

I would like to replace all cells containing either #DIV/0!, #N/A, or FALSE with 0,
using a VBA macro.

Thank you in advance

Configuration: Windows XP / Internet Explorer 8.0

3 answers

  • Votes
  • Date
  • Descending date
  1. ccm81
    Hello

    are you sure you want to do this with a macro?

    if so maybe this 

    Public Sub InitErr() For Each c In Selection If IsError(c.Value) Then c.Value = 0 Else If c.Value = False Then c.Value = 0 End If End If Next c End Sub

    have a good day
    1. aaa
      Can I do this without a macro???

      Thank you in advance
  2. ccm81
    ou if you only want to handle errors DIV/0 and #NA 

    Public Sub InitErr() For Each c In Selection If IsError(c.Value) Then errval = c.Value If errval = CVErr(xlErrDiv0) Or errval = CVErr(xlErrNA) Then c.Value = 0 End If Else If c.Value = False Then c.Value = 0 End If End If Next c End Sub

    have a good continuation
    1. ccm81
      yes, but
      you need an additional column
      if we assume that the relevant cells are in A5:Axx
      B5=IF(ISERROR(A5),0,IF(NOT(A5),0,A5))
      to drag down
      then select B5:Bxx
      Copy/paste special/Values onto A5:Axx

      as for the macro
      Alt-F11
      Insert/module
      Copy/paste the macro code
      Return to the sheet
      Tools/Macro/macros
      Select the macro
      Options/Keyboard shortcut ctrl+i for example/OK
      Usage
      select the range to process/ctrl+i

      good luck