View original (French)

Replace cells containing #DIV/0!

aaa -  
ccm81 Posted messages 11033 Status Member -
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

  1.
    ccm81 Posted messages 11033 Status Member 2 434
     
    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
    0
    1. aaa
       
      Can I do this without a macro???

      Thank you in advance
      0
  2. ccm81 Posted messages 11033 Status Member 2 434
     
    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
    0
  3. ccm81 Posted messages 11033 Status Member 2 434
     
    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
    0