IF Condition To Check Whether Absolute Value Is Greater Than Other Absolute Values

[aaron.mendes]

Hi,

I'm very new to writing codes and i'm trying to incorporate a Code in my macro

The logic goes like this - Absolute value of Column N should not be greater than the absolute amount in column M or Column R.

If the amount is greater, then i need a comment in a column stating "ERROR". If it is equal to or less than, then i need a comment stating "OK"

I have attached my excel data with the macro that I have created.
The code that is required is one of the last codes that I require to complete my macro.

Appreciate your assistance.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886 (https://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727 (https://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

You don't need a greater than or lesser than conditional VBA. You can just build a formula using VBA instead, and use it in column W (or the last empty column)




Sub GetErrorOK()

With Worksheets("ccm_dispute_results")
.Range("W2:W" & .Cells(.Rows.Count, 1).End(xlUp).Row).Formula = "=IF(OR(ABS(N2)>ABS(R2),ABS(N2)>ABS(M2)),""Error"",""OK"")"
End With

End Sub



Anyway, since your query was Excel related, moving this thread to the Excel Help section. Also, please try to give a more meaningful and concise thread title, so that other people looking for similar solutions will be able to get search results from search engines. This can only happen if the thread title used is apt.

[aaron.mendes]

Hi, thanks for the code and renaming the Thread title appropriately.
But why would it throw an error for rows 33,36,37,39 where the numbers match for column N and Column M?

[aaron.mendes]

I guess it is due to column R being blank. But there will be many times where my column R will be blank. i.e. Zero amount.

[Excel Fox]

What would you want to do in that case?

[aaron.mendes]

I would still want the same result but keeping in mind that R column would be blank at some ocassions. Would that be possible?

[Excel Fox]

OK, so if R is empty (actually, your sheet is showing zeroes, not blanks), then you want the formula to ignore that condition, right? And what about column M? What if that is also empty?

[aaron.mendes]

Yes, i want the formula to ignore that condition for column R. Column M wont have Zeroes.

[aaron.mendes]

But i can sometimes have column N Blank (Zeroes).

[Excel Fox]

so in the above code just use
"=IF(OR(AND(ABS(R2)<>0,ABS(N2)>ABS(R2)),ABS(N2)>ABS(M2)),""Error"",""OK"")"

[Excel Fox]

What happens if N is zero? What should be the logic then?

[aaron.mendes]

Just want to summarize.

N column which is the amount should not be greater than any of the columns (M or R) irrespective of the signs (-ve or +ve) or Zeroes/Blanks in the columns.

[aaron.mendes]

That worked correct after incorporating with all other codes.
what code would be required if i want to create a pivot and then paste the pivots on an email body. Is this possible through a code?

[Excel Fox]

But a zero value N column is possible. I think my formula above should work.

EDIT: I posted the above without knowing OP had responded.

The queries regarding pivot table an mailing is digressing from the original question. So please start a new thread for that.