Replace With Zero , but Keep existing Zeros

[Russ At Index]

Hello Excellers ,

Could someone shed some light on this little issue I have:

I have data b3 : h6

342 251 0 0 0 77 194
198 254 0 0 0 0 187
278 0 0 0 0 77 189
249 216 0 0 0 78 190

I would like to replace any cell value less than 100 with zero , at the same time keeping the
cells that are populated with zero.

Many thanks

Russ

 

[steve the fish]

A formula?

=IF(B6<100,0,B6)

 

[Joe4]

Here is some VBA code that will fix your whole range at once.

Sub ReplaceNums()

    Dim myRange As Range
    Dim cell As Range
    
'   ***SPECIFY RANGE TO APPLY TO***
    Set myRange = Range("B3:H6")
    
    Application.ScreenUpdating = False
    
'   Loop through all cells in range
    For Each cell In myRange
        If cell.Value > 0 And cell.Value < 100 Then
            cell.Value = 0
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Marcelo Branco]

Maybe...

Sub aTest()
    With Range("B3:H6")
        .Value = Evaluate(Replace("=IF(@<>0,IF(@<100,0,@),@)", "@", .Address))
    End With
End Sub

M.

 

[steve the fish]

Heres a bit of code that should work but any formulas are gone:

Dim x As Variant

Set x = Range("B3:H6")
Range("B3:H6") = Evaluate("=IF(" & x.Address & "<100,0," & x.Address & ")")

 

[Russ At Index]

A formula?

=IF(B6<100,0,B6)

Thanks Steve

 

[Russ At Index]

Cheers Joe4