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 !
Remove value from cell if not within a tolerance/range
- ilovepizza82
- Posts: 494
- Joined: Thu Nov 02, 2017 3:41 pm
- Location: Sewers
- Contact:
You do not have the required permissions to view the files attached to this post.
- ilovepizza82
- Posts: 494
- Joined: Thu Nov 02, 2017 3:41 pm
- Location: Sewers
- Contact:
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
- Posts: 494
- Joined: Thu Nov 02, 2017 3:41 pm
- Location: Sewers
- Contact:
Why can't I edit my original post ?
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
You do not have the required permissions to view the files attached to this post.
- ilovepizza82
- Posts: 494
- Joined: Thu Nov 02, 2017 3:41 pm
- Location: Sewers
- Contact:
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)
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
- Posts: 494
- Joined: Thu Nov 02, 2017 3:41 pm
- Location: Sewers
- Contact:
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
dist1 - the most recent race
tt1 - the most recent race
You do not have the required permissions to view the files attached to this post.