Replace greater then 0 macro

[andonny]

Hi,
I am looking for a macro to replace everything greater ten 0 in the range A1:D500.

I appreciater your help
andonny

 

[ravishankar]

Hi
what would you like zero replaced with?
Ravi

 

[Fr33dan]

Sub removeZero()
    Application.ScreenUpdating = False
    With Range("A1:D500")
        For i = 1 To .Rows.Count
            For j = 1 To .Columns.Count
                With Range("A1:D500")(i, j)
                    If .Value > 0 Then
                        .Value = 0
                    End If
                End With
            Next j
        Next i
    End With
End Sub

Just replace the = 0 with = (what you want to put in the cells)

 

[Kenneth Hobson]

In t(), I replace cells > 0 with 0. You can set it as you like.

Sub t()
  RangeFindReplace [A1:B500], ">", 0, 0
End Sub

Sub RangeFindReplace(theRange As Range, Operator As String, vFind As Variant, vReplace As Variant)
  Dim cell As Range
  For Each cell In theRange
    Select Case Operator
      Case Is = "="
        If cell.Value = vFind Then cell.Value = vReplace
      Case Is = ">="
        If cell.Value >= vFind Then cell.Value = vReplace
      Case Is = "<="
        If cell.Value <= vFind Then cell.Value = vReplace
      Case Is = ">"
        If cell.Value > vFind Then cell.Value = vReplace
      Case Is = "<"
        If cell.Value < vFind Then cell.Value = vReplace
    End Select
  Next cell
End Sub