• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If and Greater Than Code Required

aaron.mendes

New Member
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 B should not be greater than the absolute amount in column A or Column C


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"


Appreciate your assistance.
 
Aaron


Firstly, Welcome to the Chandoo.org forums


Try: =IF(OR(ABS(B1)<=ABS(A1),ABS(B1)<=ABS(C1)),"Ok","Error")
 
As Hui wrote it, a formula in the worksheet would be best. If you need to do it in VB, here's the structure:

[pre]
Code:
Sub SampleTest()
If Abs(Range("B2")) > Abs(Range("A2")) Or _
Abs(Range("B2")) > Abs(Range("C2")) Then

MsgBox "Ok!"
Else
MsgBox "Error!"
End If

End Sub
[/pre]
 
Hi, aaron.mendes!


Taking Luke M's code if you want to use it as a UDF (user defined function, i.e., they can be used in the worksheet as other native embedded formulas), try this:


-----

[pre]
Code:
Option Explicit

Function SampleTest(Cell1 As Range, Cell2 As Range, Cell3 As Range) As String
Dim A As String
If Abs(Cell2.Value) <= Abs(Cell1.Value) Or Abs(Cell2.Value) <= Abs(Cell3.Value) Then
A = "Ok!"
Else
A = "Error!"
End If
SampleTest = A
End Function
[/pre]
-----


Usage: =SampleTest(A2,B2,C2)


Regards!


EDITED


PS: Reading again your 1st post I think Luke M's messages are inverted.
 
Hi, aaraon.mendes!

Just fyi, if you want to perform a control of what's entered in cell B2 (or column B) you could use data validation. Try this:

a) Select B2 cell.

b) Go to Data tab, Data Tools group, Data Validation icon.

c) In 1st tab Setup, Criteria, Allow drop down box, select:

d) If not, Custom, and set Formula as =AND(ABS(B2)<=ABS(A2),ABS(B2)<=ABS(C2))

e) You may set proper Input and Error messages if required.

Regards!
 
Back
Top