VBA - Clear cell if value within any individual set of ranges nested in another range

[teacoffee]

Hello. I've been scouring the internet looking for an example of this and I have not found anything similar to what I'm trying to do. I have this table below:

Test	Test Against
4.015​	1.011​
1.010​	2.022​
3.031​	3.009​
5.050​	4.011​
2.020​	5.001​

I'm trying to run a loop starting at the first cell in the Test column moving downward one cell at a time and testing if the selected cell's value is within +/- 0.020 of ANY of the numbers in the Test Against column. If the selected number is not within any range applied to each cell's value in Test Against, then I would like to clear the contents of that Test cell. I've seen ways to test if the exact value of a selected cell is in another range, but not a way to test against only fixed +/- ranges applied to every individual cell in the range being tested against.

In this example, I would like to write a script that clears the contents of only cells 3.031 and 5.050 because neither of those values fall within +/- 0.020 of any cell's value in the Test Against column.

Thank you ahead of time for any help with this.

 

[mse330]

Welcome to the forum,

Try below code ... Assuming your data starts from cell A1

Sub test()

Dim a
a = [A1].CurrentRegion

For x = 2 To UBound(a)
   For y = 2 To UBound(a)
      If Abs(a(x, 1) - a(y, 2)) < 0.02 Then Exit For
      If y = UBound(a) Then a(x, 1) = ""
   Next
Next

[A1].Resize(UBound(a), UBound(a, 2)) = a

End Sub

 

[teacoffee]

Thank you. I got this to work for my real data as well.