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

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
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!

Code:
=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!
 
Last edited:
I ended up just erasing the entire code and bringing all the original data into the cells I wanted, then CLEANed and TRIMmed them... Even though I had accounted for this.... in any case... it worked. No more VALUE! errors. So thank you!

However, a problem that I know is probably just commonsense to you experts... is my failure when creating loops.... The highlighted code below, its the same in every single row... (obviously because I tell it to be the same each time). I don't know how to tell the code to compare the cells of the row it's examining.

i.e.

=IF(AL3=AJ3,TRUE,FALSE)
=IF(AL4=AJ4,TRUE,FALSE)
=IF(AL5=AJ5,TRUE,FALSE)

I'm guessing I need to add additional variables?

Code:
Sub Weights()


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim myLastRow As Long
Dim myWorksheet As Worksheet
Dim myLastRow2 As Long
Dim myWorksheet2 As Worksheet


Set myWorksheet = Worksheets("HazShipper")
Set myWorksheet2 = Worksheets("DGbyFLT")


myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row
myLastRow2 = myWorksheet2.Cells(myWorksheet2.Rows.Count, "A").End(xlUp).row


Range("AJ2").Formula = "=P2"
Range("AK2").Formula = "=S2"
Range("AL2").Formula = "=IFERROR(VLOOKUP(U2,DGbyFLT!$A$2:$BA$" & myLastRow2 & ",53,0),""No Value"")"
Range("AM2").Formula = "=CLEAN(TRIM(AJ2))"
Range("AN2").Formula = "=CLEAN(TRIM(AK2))"
Range("AO2").Formula = "=CLEAN(TRIM(AM2))"


Range("AJ2:AO2").Select
Selection.AutoFill Destination:=Range("AJ2:AO" & myLastRow)


    Columns("AM:AO").Select
    Range("AO1").Activate
    Selection.Copy
    Range("AJ1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AM:AO").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AM2").Select


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(, 2).Formula = "=IFERROR(IF([B][COLOR=#ff0000]AL2=AJ2[/COLOR][/B],TRUE,FALSE),""ERROR"")"
    Else
        mycell.Offset(, 2).Formula = "=IF([B][COLOR=#ff0000]AL2=AJ2[/COLOR][/B],TRUE,FALSE)"
    End If


Next


End Sub
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Code:
.....
.....
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(, 2).Formula = "=IFERROR(IF([B][COLOR=#ff0000]AL2=AJ2[/COLOR][/B],TRUE,FALSE),""ERROR"")"
    Else
        mycell.Offset(, 2).Formula = "=IF([B][COLOR=#ff0000]AL2=AJ2[/COLOR][/B],TRUE,FALSE)"
    End If

Next
.....
.....
I think changing your If..Else..EndIf block to this will do what you want...
Code:
[table="width: 500"]
[tr]
	[td]    If mycell.Value = "L" And mycell2.Value = "UN3363" Then
        mycell.Offset(, 2).Formula = "=IFERROR(IF([B][COLOR=#ff0000]AL" & i & "=AJ" & i & "[/COLOR][/B],TRUE,FALSE),""ERROR"")"
    Else
        mycell.Offset(, 2).Formula = "=IF([B][COLOR=#ff0000]AL" & i & "=AJ" & i & "[/COLOR][/B],TRUE,FALSE)"
    End If[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Perfect. Thank you all for your continued support and solutions! I promise I'll take that looping class this weekend! :):cool:
 
Upvote 0
Code:
    Else
        mycell.Offset(, 2).Formula = "=IF(ABS(AJ2-AL2) <= AL2*0.1, TRUE, FALSE) "
@Rick Rothstein I've tried every combination of "i" based on your previous solution I could think of.

Code:
        mycell.Offset(, 2).Formula = "=IF(ABS(AJ" & i" - AL" & i") <= AL" & i"*0.1, TRUE, FALSE)"

Perhaps a lesson on the proper uses of "&" are necessary as well. I thought "&" meant specifically that..... AND

AJ & i (current row) minus AL & i (current row)

What logic am I not following here?
 
Last edited:
Upvote 0
You are missing a few ampersands. Every time you join a variable to literal text, you need one.
Code:
        mycell.Offset(, 2).Formula = "=IF(ABS(AJ" & i [COLOR=#ff0000][B]&[/B][/COLOR] " - AL" & i") <= AL" & i [B][COLOR=#ff0000]&[/COLOR][/B] "*0.1, TRUE, FALSE)"
 
Upvote 0
You are missing a few ampersands. Every time you join a variable to literal text, you need one.
Code:
        mycell.Offset(, 2).Formula = "=IF(ABS(AJ" & i [COLOR=#ff0000][B]&[/B][/COLOR] " - AL" & i") <= AL" & i [B][COLOR=#ff0000]&[/COLOR][/B] "*0.1, TRUE, FALSE)"

In the knowledge bucket. Thank you!
 
Upvote 0
At the risk of making it more confusing, here's another option:

Rich (BB code):
    mycell.Offset(, 2).Formula = "=IF(ABS(AJ" & i & " - AL" & i & ") <= AL" & i & "*0.1, TRUE, FALSE)"
    mycell.Offset(, 2).FormulaR1C1 = "=IF(ABS(RC[-3] - RC[-1]) <= RC[-1]*0.1, TRUE, FALSE)"
If mycell refers to a cell in column AK, and i is the row, then those 2 lines are equivalent. You can often use relative referencing to shorten the formula and eliminate quotes and ampersands. Excel will convert the formula to the standard A1 style formula on the sheet (if you have the setting that way).
 
Upvote 0
At the risk of making it more confusing, here's another option:

Rich (BB code):
    mycell.Offset(, 2).Formula = "=IF(ABS(AJ" & i & " - AL" & i & ") <= AL" & i & "*0.1, TRUE, FALSE)"
    mycell.Offset(, 2).FormulaR1C1 = "=IF(ABS(RC[-3] - RC[-1]) <= RC[-1]*0.1, TRUE, FALSE)"
If mycell refers to a cell in column AK, and i is the row, then those 2 lines are equivalent. You can often use relative referencing to shorten the formula and eliminate quotes and ampersands. Excel will convert the formula to the standard A1 style formula on the sheet (if you have the setting that way).
At the risk of making even more confusing, why not eliminate the loop altogether? Couple that together with seeing no need to look specifically for a set of conditions where errors may be encountered just so you can handle the error and couple that with the fact the and IF function call is unneeded to simply return TRUE/FALSE for a logical expression and we get the following single line of code which should be able to replace the whole of the OP's For..Next block of code...
Code:
Range("AK2:AK" & myLastRow).FormulaR1C1 = "=IFERROR(RC36=RC38,""ERROR"")"
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top