ARE VALUES WITHIN 1/10TH OF EACH OTHER? How to?

[Sphinx404]

A2 = 0.9464
B2 = 0.9

OR

A2 = 0.14785
B2 = 0.1

How does one write a VBA code stating that if A2 is within .1 of B2, then TRUE, else FALSE?

Thanks folks!

=AND(A2<=B2+0.1,A2>=B2-0.1)

The above code continues to give me :#VALUE! and it's irritating me to no end. I've cleaned, trimmed,..... etc... AHHHH!

 

[Scott Huish]

That is not VBA code, it is an Excel formula and there is an easier way.

Sub test()
MsgBox Abs(Range("B2") - Range("A2")) <= 0.1
End Sub

This would be the equivalent worksheet formula:
=ABS(A2-B2)<=0.1

 

[B___P]

Hi Sphinxs404,
give this a try

IF(ABS(A2-B2) <= B2*0.1, TRUE, FALSE) <b2*0.1,true,false)<b2*0.1,true,false)

hope this helps</b2*0.1,true,false)<b2*0.1,true,false)

 

[Rick Rothstein]

This would be the equivalent worksheet formula:
=ABS(A2-B2)<=-0.1

Typo alert... the highlighted minus sign should be deleted.

 

[Scott Huish]

Typo alert... the highlighted minus sign should be deleted.

Thanks, it is fixed.

 

[Sphinx404]

I've tried all the suggestions in this thread. All I ever get returned is: #VALUE !

=IF(ABS(A2-B2) <= B2*0.1, TRUE, FALSE) - returns #VALUE !
=ABS(A2-B2)<=0.1 - returns #VALUE !

ACTUAL CODE:

Sub weights()


Dim myWorksheet As Worksheet
Dim myLastRow As Long
Dim mycell As Range
Dim mycell2 As Range


Set myWorksheet = Worksheets("HazShipper")
myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row


For i = 2 To myLastRow


Set mycell = myWorksheet.Range("AK" & i)
Set mycell2 = myWorksheet.Range("AD" & i)


  If mycell.Value = "L" And mycell2.Value <> "UN3363" Then
      mycell.Offset(, 3).Formula = "=IF(ABS(AJ2-AL2) <= AL2*0.1, TRUE, FALSE) " [COLOR=#008000]'I don't see a mathematical error here[/COLOR]
  Else
      mycell.Offset(, 3).Formula = "=IF([B][COLOR=#ff0000]AL2=AJ2[/COLOR][/B],TRUE,FALSE)"
  End If


Next


End Sub

Also, this is a bone head mistake on my part, but the highlighted portion in red.... I need it to change per the row number I am inserting the formula on... it just continually comes up comparing the same 2 cells over and over... do I have to create variables for these cells as well?

 

[Joe4]

I've tried all the suggestions in this thread. All I ever get returned is: #VALUE !

=IF(ABS(A2-B2) <= B2*0.1, TRUE, FALSE) - returns #VALUE !
=ABS(A2-B2)<=0.1 - returns #VALUE !

That usually means that you do not have numeric values in the cells you are comparing.
Are you sure that you are referencing the correct cells and do not have text in one or both?

If you have the values that you show up in your original post, and manually enter these formulas in any cell, does it work?
If so, then that confirms that the issue is not with the formula.

 

[Sphinx404]

That usually means that you do not have numeric values in the cells you are comparing.
Are you sure that you are referencing the correct cells and do not have text in one or both?

If you have the values that you show up in your original post, and manually enter these formulas in any cell, does it work?
If so, then that confirms that the issue is not with the formula.

@Joe4

Hmmm... the simple answer is Yes... there is text. I used IFERROR in an earlier formula in "AJ" that will return "No Value".

Forgive my ignorance, but the text is in 238 cells within a column that has 20K rows of numerical data. Do the 238 rows of text throw the formula off entirely? I would figure that it would just return an error on these specifically.

You can view the data here: https://drive.google.com/open?id=1SbSFjReI08O4WMAzpgEBPFexsOfF_wE4

I've selected both columns "AJ" & "AL" and made sure that they are numerical, not set as TEXT or GENERAL.... not that I think it matters... but with my ignorance on these types of specifics... maybe it does.

The formula for "AL" is: Range("AL2").Formula = "=IFERROR(VLOOKUP(U2,DGbyFLT!$A$2:$BA$" & myLastRow & ",53,0),""No Value"")"

 

[Joe4]

The #VALUE message is actually an error message.
If you might have text (like "No Value") in the cells you are using that function on, you will get those errors message.
So, you will need to determine how you want to handle those error situations. You could wrap the formulas Rick and Scott provided in an IFERROR, like you did for the VLOOKUP, if you like.

 

[Sphinx404]

Thanks for your patience @Joe4

I'll get to work on trying to fix this and let you know how it turns out.