I want to check the numbers in Col-C one at a time against the numbers within the range (A1:A12)i n Col-A. If the Col-B number is within a tolerance of any Col-A number, I want to store the Col-A number in Col-D. If the Col-B number not within the tolerance of any number in Col-A, then store the Col-C number in Col-D.
note: There are duplicate numbers in Col-A and not all cells in Col-A range have numbers in them. The Col-C numbers will always be filled with numbers.
attached is file with data setup on Sheet1, and the macro.
The line I am having problems with is....
If c.Value + Toler > x1(i) And c.Value - Toler < x1(i) Then
1) I need to correct the code in the line above.
2) Any suggestions on a better approach would be greatly appreciated.
SOLVED BY AUTHOR
HERES WHAT I CAME UP WITH
Sub ReplaceNosInTolerance()
' source data must not have gaps in rows from top
' all 12 vvalues not neccessary, but blanks only at bottom
' 2011-02-07
'
Dim A(12), x1(16), x2(16)
Dim aTol
Range("d1:d16").ClearContents ' Change to suit
On Error Resume Next
For i = 1 To 12
A(i) = Cells(i, 1).Value
Next i
For i = 1 To 16
x1(i) = Cells(i, 3).Value
Next i
'Stop
aTol = 0.25
For j = 1 To 16
For i = 1 To 12
If A(i) + aTol > x1(j) And A(i) - aTol < x1(j) Then
Range(Cells(j, 4).Address).Value = A(i)
GoTo Here
Else
End If
Next i
Here:
If Cells(j, 4) = "" Then
Range(Cells(j, 4).Address).Value = x1(j)
End If
Next j
'Stop
Erase A, x1, x2
End Sub
Bookmarks