help with IF statement

[Luis939]

Hey, im trying to visit a range of cells, but some cells have a formula such as =D1/D2, but lets say D2 is a zero, then the cell displays "DIV/0!", so i want to say
that if the cell equals that value then to simply replace it with a dash (-), so i put
if activecell.value = "DIV/0!" then
activecell.value = "-"
end if

i've even tried activecell.formula = "DIV/0!", but i keep getting a TYPE MISMATCH error every time
so how do i say around this so that it recognizes the value div/0!, not the formula behind it, thanks

 

[Zathras]

The easiest way around this is to use a slightly different formula when entering a division operation:

  =IF(D2,D1/D2,"-")

 

[RobBroekhuis]

Try using

if iserror(activecell) then activecell="-"

Rob

 

[Zathras]

To follow up with your original approach, the correct way would be:

   If ActiveCell.Text = "#DIV/0!" Then
     ActiveCell.Value = "-"
   End If

That will catch the specific error of divide by zero. If you want to catch any error (e.g., the square root of a negative number which gives #NUM!), the Rob's method wourd be preferred.

But, I have to ask. Why would you want to replace a formula with a hard-coded hyphen? What about when D2 is no longer zero? Would you not then want to see the correct division result?

 

[Luis939]

well the people i work with have a template where this cell contains the division of those 2 numbers, but sometimes those numbers are either both zero or they have no values for these numbers, so if they dont have any values they want a "-" if theres no value for the cell, so i was having trouble trying to do that