Results 1 to 1 of 1

return number from range if in tolerance

Threaded View

  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Bakersfield, CA
    MS-Off Ver
    Excel 2003
    Posts
    37

    return number from range if in tolerance

    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
    Attached Files Attached Files
    Last edited by BHudPE; 02-07-2011 at 07:53 PM. Reason: solved it myself

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1