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

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.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=312886#p312886
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=312727#p312727
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?p=312533#p312533
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
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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)

Code:

---


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.

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?

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.

What would you want to do in that case?

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

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?

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

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

so in the above code just use

Code:

---

"=IF(OR(AND(ABS(R2)<>0,ABS(N2)>ABS(R2)),ABS(N2)>ABS(M2)),""Error"",""OK"")"

---

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

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.

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?

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.