CODEWITHSUNDEEP
vba
bear21
bear21

Reputation: 11

Replacing "0"s with blank cells after running formula vba

Attached is a screenshot but I want to replace 0's with a blank text after I run the Min formula in my vba macro. Currently, I have done 

For Each cell In Range("D4:D400")
If cell.Value = "0" Then cell.Clear
Next

and this has cleared out every single value in the column.

Data Sample

EDIT

Cells(PoleRow, 4).Formula = "=MIN($AK$" & PoleRow & ":$CH$" & PoleRow & ")"
Cells(PoleRow, 5).Formula = "=D" & PoleRow & "-3.33"
Range(Cells(PoleRow, 3), Cells(PoleRow, 5)).Borders(xlEdgeLeft).Weight = xlThick
Range(Cells(PoleRow, 3), Cells(PoleRow, 5)).Borders(xlEdgeRight).Weight = xlThick

Upvotes: 0

Views: 122

Answers (1)

Mikku
Mikku

Reputation: 6664

I will suggest strongly against to use the cell as they are already defined object in Excel. Use instead cel or cl or something similar for naming.

In your case

For Each cel In Range("D4:D400")
    If cel.Value = 0 Then cel.Clear
Next

will work. Or you can just use the Excel Function IfError

=IfError(1/1/(Your Formula), "") 'Courtesy Scott Craner

Or Use If Condition

 =IF(Your Formula <> 0, Your Formula, "")

Code:

If Evaluate("=MIN($AK$" & PoleRow & ":$CH$" & PoleRow & ")") = 0 Then
    Cells(PoleRow, 4) = ""
    Cells(PoleRow, 5) = "-3.33"
Else
    Cells(PoleRow, 4).formula = "=MIN($AK$" & PoleRow & ":$CH$" & PoleRow & ")"
    Cells(PoleRow, 5).formula = "=D" & PoleRow & "-3.33"
End If

Range(Cells(PoleRow, 3), Cells(PoleRow, 5)).Borders(xlEdgeLeft).Weight = xlThick
Range(Cells(PoleRow, 3), Cells(PoleRow, 5)).Borders(xlEdgeRight).Weight = xlThick

Upvotes: 1

Related Questions