Find and replace value if greater than.

[premjeetgupta]

Hi,

I want to find and replace value if greater than 0 to 0 for certain range. Following code I have found on google search. But issue with the code is it change value as "0" for the text as well which are in the range. Please help.


Sub FindReplace()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value > 0 Then
Rng.Value = 0
End If
Next
End Sub

 

[PCosta87]

Hi,

Try the following (it is using the range A1:A20 but you can change to whatever range you need):

Sub Changeto0()

    Dim c As Range
   
    For Each c In Range("A1:A20")
        If IsNumeric(c) = True And c > 0 Then
            c = 0
        End If
    Next c

End Sub

Hope this helps

 

[premjeetgupta]

Thanks @PCosta87

I used following code. This works for me as well.
I missed IsNumeric func.
Thanks for your support.

Sub FindReplace()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If IsNumeric(Rng) Then
Rng.Value = 0
End If
Next
End Sub

 

[PCosta87]

You are welcome