We help IT Professionals succeed at work.

Simple VBA Code to check if the ABS difference between two Values exceeds a third Value (EXCEL Application)

207 Views
Last Modified: 2017-04-30
Example Data
        A       B       C
1     300  305   15
2     600  608   15
3     900  888   15
4   1200 1207  15
5   1500 1486  15
6
7    1500 1486  15
8    1200 1207  15
8      900  888   15
10    600  608   15
11   300  305   15

I need code that determines: If(ABS(A1-B1))<=C1,"PASS","FAIL"

But it needs to do this for the entire range for rows 1-5 and 6-11.  I need one output from the analysis:  "Pass" or "FAIL".  If any one fails the entire data set fails.  If they all fall within, then it is a "PASS"
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi David,

Try below:
Sub UpdateRange()
Dim Ws As Worksheet
Dim LR As Long
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Range("D1:D" & LR).FormulaR1C1 = "=IF(AND(RC1="""",RC2="""",RC3=""""),"""",IF(ABS(RC1-RC2)<=RC3,""PASS"",""FAIL""))"
End Sub

Open in new window

Update-Range_ABS.xlsm
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks, SHUMS.  I'm not sure I know enough to modify your code to get me working. I've attached my spreadsheet.

My data is on the "datasheet".  The criteria is on the "calculations" sheet.  

I need to compare:
NOMINAL (datasheet!C9) value to the Rdg after Tap (datasheet!G9) and it must be <= Tolerance Band (calculations!F3).  
NOMINAL (datasheet!C9) value to the Rdg after Tap (datasheet!M9) and it must be <= Tolerance Band (calculations!F3).

Rows 9-13 (Increasing) need to be compared against the tolerance band F3-F7.  BUT - going the other way:
Rows 15-19 (decreasing) need to be compared against the tolerance band F7-F3.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
No attachment :(
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
David,

Your sheet is already formulated, why you need VBA? its not even huge data in it.

Author

Commented:
Datasheet Cell  E28 needs to either have "PASS" or "FAIL" based on reviewing 20 sets of numbers compared against the tolerance.  I didn't know an easy way to do it.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You said VBA so...
Sub AbsCheck()

Dim lngRow As Long
    
For lngRow = 1 To 11
    If Abs(Cells(lngRow, "A") - Cells(lngRow, "B")) > Cells(lngRow, "C") Then
        MsgBox "Fail at row " & lngRow
        Exit Sub
    End If
Next
        
MsgBox "Pass"
End Sub

Open in new window

Author

Commented:
I believe NEERAJ's formula above may do the trick.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Or as a User Defined Function
Function AbsCheck(r As Range) As String

Dim lngRow As Long
    
For lngRow = r.Row To r.Rows.Count
    If Abs(r.Cells(lngRow, "A") - r.Cells(lngRow, "B")) > r.Cells(lngRow, "C") Then
        AbsCheck = "Fail"
        Exit Function
    End If
Next
        
AbsCheck = "Pass"
End Function

Open in new window

Author

Commented:
=IF(SUMPRODUCT(--(ABS(A1:A5-B1:B5)<=C1:C5))=ROWS(A1:A5),"Pass","Fail")
I was able to use this formula to accomplish my task.  Thank you to everyone who made suggestions!
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.