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!

 

[Sphinx404]

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?

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

 

[Rick Rothstein]

.....
.....
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...

[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]

 

[Sphinx404]

Perfect. Thank you all for your continued support and solutions! I promise I'll take that looping class this weekend!

 

[Sphinx404]

    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.

        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?

 

[Joe4]

You are missing a few ampersands. Every time you join a variable to literal text, you need one.

        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)"

 

[Sphinx404]

You are missing a few ampersands. Every time you join a variable to literal text, you need one.

        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!

 

[Eric W]

At the risk of making it more confusing, here's another option:

    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).

 

[Rick Rothstein]

At the risk of making it more confusing, here's another option:

    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...

Range("AK2:AK" & myLastRow).FormulaR1C1 = "=IFERROR(RC36=RC38,""ERROR"")"