Remove value from cell if not within a tolerance/range

[ilovepizza82]

Hi,

Is there a way to remove values if they are not in certain range ?
Its a bit annoying to be getting mixed incorrect averages only because a dog had raced in a shorter or longer distance before.
I mean the values must be within certain tolerance say not greater and less than 2 seconds. If so remove from a cell completely.

What should i add in this macro to make it work:

Sub ReplaceWrongValueWithBlank()
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select One Range That You Want to Convert:", "ReplaceWrongValueWithBlank", myRange.Address, Type:=8)
For Each oneCell In myRange
If oneCell.Value = ... Then
oneCell.Value = ""
End If
Next
End Sub

Thanks in advance !

[ilovepizza82]

I've just realized it might be impossible to do it as range would have to be defined somwhere...and also cant edit my original post 4 some reason either lol. Great.

[ilovepizza82]

Why can't I edit my original post ?

[ODPaul82]

If you have the cells selected then Selection will represent them, you just need to run the code via the "Run macros" section or with the VBA window open seperately

Code: Select all

Sub ReplaceValueInRange()
    Dim r As Excel.Range
    
    Const MIN_VAL As Long = 4
    Const MAX_VAL As Long = 7
    
    For Each r In Selection
        ' Want to be ensuring its a numeric value
        If IsNumeric(r.Value) Then
        
            If r.Value > MAX_VAL Or r.Value < MIN_VAL Then r.Value = "Beer me up"
        
        End If
    Next r

    Set r = Nothing
End Sub

replace_before.png

replace_after.png

[ilovepizza82]

THanks Paul. I really do owe you a beer
However it still requires manual entering of the range so it kinda works but actually isnt.
Eg: each race is different, can be 460m or 300m, there are dozes of different types of races each day and each has its own dog speed range.
Like i said i dont think its possible Paul.
It would have to say check specific cell eg.
IF its "29.32" THEN range min=28.9 max=30.1
IF "16.2" THEN range min=16 max=17
IF not then "" (empty cell)

[ilovepizza82]

Or perhaps I could somehow link the current race distance (in blue) with the previous ones (same distance) and if not the same just empty the cells ...
dist1 - the most recent race
tt1 - the most recent race