Re: How 2 avoid multiple IF's by LOOKUP's???

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:D"
Const Err1 As String = "Maturity date has passed"
Const Err2 As String = "Account Balance is exceeding the limit"
Const Err3 As String = "Amount is negative"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "E").Value = ""
If Me.Cells(.Row, "D") < 0 Then _
Me.Cells(.Row, "E").Value = Err3
If Me.Cells(.Row, "B") < Me.Cells(.Row, "D") Then _
Me.Cells(.Row, "E").Value = Err2
If Me.Cells(.Row, "C").Value < Date Then _
Me.Cells(.Row, "E").Value = Err1
'etc
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is work*** event code, which means that it needs to be
'placed in the appropriate work*** code module, not a standard
'code module. To do this, right-click on the *** tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"FARAZ QURESHI" <FARAZQURESHI@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:956A026E-7CFB-44F7-AC9B-909D8F5E98DD@xxxxxxxxxxxxxxxx
Thanx Bob,

I am sorry but there seemed to be some problem with my net and probably
the
page was not refreshing. I thought that my question had not be posted due
some problem. Thus, I had the question replaced.

By the way can I create a MACRO as well, creating some sort of a custom
function or some other way around, so that the risk of any erroneous
formula
in even one of the cell in column E could be avoided due to copying down a
lengthy one in all?

Once again I am sorry and beg your pardon.

Faraz!

"Bob Phillips" wrote:

Don't see how you could avoid it.

You could two step it with a helper cell say

=IF(C2<TODAY(),1,IF(D2>B2,2,IF(D2<0,3,0)))

and then lookup the message with

=IF(E2>0,INDEX*Sheet2!B:B,MATCH(E2,Sheet2!A:A,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"FARAZ QURESHI" <FARAZQURESHI@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:733287C4-27F4-44A4-B923-EBBD3157F866@xxxxxxxxxxxxxxxx
Hello every1,

The actual situation is quite a detailed and complex one but lets start
with
a simple example:

The "*** 1" is to be filled up with several columns like:
A B C D E
1 Case Limit Maturity-Date Balance Error
2 abc 100 12/12/2001 80 Maturity date has
passed
3 def 3000 12/12/2009 4000 Account Balance is
exceeding the limit
4 ghi 5000 12/12/2008 -200 Amount is negative

"*** 2" has a list like:
A B
1 Error Code Message
2 1 Account Balance is exceeding the limit
3 2 Maturity date has passed
4 3 Amount is negative

I want the column E of *** 1 to search out the error code
automatically
from *** 2 and return the message present in the corresponding cell
in
column B.

Actually there are several multiple error checks that I want to set out
but
typing out a lengthy formula of IF's is nearly impossible and risky.
Conditional formating shall also only highlight the cell but not
displaying
the message and also involves the same problem of lengthy formula.

Any way out, PREFERABLY a lookup sort of formula for its applicability
upon
all the the computer without lowering the macro security level,
otherwise
even a macro shall be gratefully accepted! All your advices shall be
valuable.

Thanx in advance,

FARAZ!





.