hi
i want a vba macro code for finding
divide by zero error in one column (R2:R20) and then put blank instead of it , i have many sheets and i have to repeat this action for all so it will be better to do it with vba code please help to find correct syntax
Sub RepUnd()
Columns("R").Replace "*/0", "":
End Sub
THis piece of code will look in the selected range and replace a div error it ignores any other text or errors as far as i can see
Sub ReplaceDivError()
Dim r As Range
Dim c As Range
Set r = Selection
For Each c In Selection
Debug.Print c.Address
If IsError(c.Value) Then
If c.Value = CVErr(xlErrDiv0) Then
c.Value = ""
End If
End If
Skip:
Next c
End Sub
hi
first of all thank you for your help
and could you please change code a bit ( Set r = Selection)
selection have to be constant :R2 up to maximum R50
You only want to look in column R? Sorry missed that bit
just change the selection to
set r = range("r2:r50")
Or if you want to make it dynamic as column r gets longer
Sub ReplaceDivError()
Dim r As Range
Dim c As Range
Dim lr As Integer
lr = Cells(Rows.Count, "r").End(xlUp).Row
Set r = Range("r2:r" & lr)
For Each c In r
If IsError(c.Value) Then
If c.Value = CVErr(xlErrDiv0) Then
c.Value = ""
End If
End If
Next c
End Sub
