If Statement
 

I have the follwing conditions:

If X = "*" And Z = "*" Then
formula here
ElseIf X = "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula here
ElseIf X = "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula here
ElseIf X < "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula here
ElseIf X < "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula here
EndIf

is ther any conflict or something wrong here?!

I find in some cases my formula do not work.

Thanks

If Statement
 

Maybe this will Help

http://www.excel-vba.com/macros/vba-...-statements.ht

--
davesexce
-----------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread.php?threadid=52401

If Statement
 

" wrote:
I have the follwing conditions:

If X = "*" And Z = "*" Then
formula[1] here
ElseIf X = "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula[2] here
ElseIf X = "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula[3] here
ElseIf X < "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula[4] here
ElseIf X < "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula[5] here
EndIf

is ther any conflict or something wrong here?!
I find in some cases my formula do not work.

Yes. Go into VBA Help and search for "operator precedence".
You will see that AND has higher precedence than OR.
Consequently, the second condition is evaluated as
(X = "*" And Z = 1) Or Z = 2 Or Z = 3, and you will always
go into "formula[2]" when Z is 2 or 3, which I suspect is
not your intent. I suspect you want
X = "*" And (Z = 1 Or Z = 2 Or Z = 3).

I also suspect that you should use AND in the 3rd and 5th
conditional expressions. But the whole thing seems messier
than necessary. I suspect the following fits your intentions.

if X = "*" then
if Z = "*" then
statements[1]
elseif Z = 2 or Z = 3 or Z = 4 then
statements[2]
else
statements[3]
end if
elseif Z = 1 or Z = 2 or Z = 3 then
statements[4]
else
statements[5]
end if

Note that "X < *" is implied if the first if-statment fails.
Similarly for the series of Z if-statements.

PS: You might also want to look at the SELECT CASE
statement.

If Statement
 

Either use parens or be certain you understand the precedence rules and
order of evaluation.

For example:
X = "*" And (Z = 1 Or Z = 2 Or Z = 3)
is true if X="*" and any one or more of the Z= terms are true
(X = "*" And Z = 1 Or Z = 2) Or Z = 3
is true if Z=3 (and in some other situations too)
if Z=3 then the value of X does not matter

On 19 Mar 2006 00:02:52 -0800, wrote:

I have the follwing conditions:

If X = "*" And Z = "*" Then
formula here
ElseIf X = "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula here
ElseIf X = "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula here
ElseIf X < "*" And Z = 1 Or Z = 2 Or Z = 3 Then
formula here
ElseIf X < "*" And Z < 1 Or Z < 2 Or Z < 3 Or Z < "*" Then
formula here
EndIf

is ther any conflict or something wrong here?!

I find in some cases my formula do not work.

Thanks

If Statement
 

Thanks for all the replies,

Some where else something is missing or having conflict:

Range D4 can have *, 1, 2, 3 and 100 thru 400
Range E4 can have *, G, and N

Based on the value in E4 and D4 different formulas are applied.

'==== Changes in formulas due to the following conditions:====

' TArl and TPhyLoc are the values affected.

' For E4 the following two values:

' If E4 < * then TArl = RC1&R4C5 ' (E4=G or N)
'If E4 = * then LEFT(TArl,3)=RC1

' For D4 the following values:

' If D4 = * then TPhyLoc not considered
' If D4 = 1 or 2 or 3 then LEFT(TPhyLoc,1) = R4C4 ' D4= First
Character of TPhyLoc
' If D4 = 100 thru 400 then TPhyLoc = R4C4 '
D4=TPhyLoc

'====

'Case = 1

If E4 = "*" And D4 = "*" Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMont h<=R5C2,IF(SalRef=""S"",ComFare,0),0),0),0),0)-SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMonth< =R5C2,IF(SalRef=""R"",ComFare,0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMont h<=R5C2,IF(SalRef=""S"",Rev,0),0),0),0),0)-SUM(IF(LEFT(TArl,3)=RC1,IF(TMonth=R4C2,IF(TMonth< =R5C2,IF(SalRef=""R"",Rev,0),0),0),0),0)"

' Case = 2

ElseIf E4 = "*" And (D4 = 1 Or D4 = 2 Or D4 = 3) Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1, IF(TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Co mFare,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1,IF (TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComF are,0),0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1, IF(TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Re v,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(LEFT(TArl,3)=RC1,IF (TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev, 0),0),0),0),0),0)"

' Case = 3

ElseIf E4 = "*" And (D4 < 1 Or D4 < 2 Or D4 < 3 Or D4 < "*") Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(Left(TArl,3)=RC1,IF(TMont h=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFare,0) ,0),0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(Left(TArl,3)=RC1,IF(TMonth =R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare,0),0 ),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(LEFT(TArl,3)=RC1,IF(TMont h=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0),0), 0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(LEFT(TArl,3)=RC1,IF(TMonth =R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0),0),0) ,0),0),0)"

' Case = 4

ElseIf E4 < "*" And D4 = "*" Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<= R5C2,IF(SalRef=""S"",ComFare,0),0),0),0),0)-SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<=R5 C2,IF(SalRef=""R"",ComFare,0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<= R5C2,IF(SalRef=""S"",Rev,0),0),0),0),0)-SUM(IF(TArl=RC1&R4C5,IF(TMonth=R4C2,IF(TMonth<=R5 C2,IF(SalRef=""R"",Rev,0),0),0),0),0)"

' Case = 5

ElseIf E4 < "*" And (D4 = 1 Or D4 = 2 Or D4 = 3) Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF( TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFa re,0),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF(TM onth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare ,0),0),0),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(left(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF( TMonth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0 ),0),0),0),0),0)-SUM(IF(LEFT(TPhyLoc,1)=R4C4,IF(TArl=RC1&R4C5,IF(TM onth=R4C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0), 0),0),0),0),0)"


' Case = 6


ElseIf E4 < "*" And (D4 < 1 Or D4 < 2 Or D4 < 3 Or D4 < "*") Then

ActiveCell.Offset(0, 2).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth= R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",ComFare,0),0) ,0),0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth=R4 C2,IF(TMonth<=R5C2,IF(SalRef=""R"",ComFare,0),0),0 ),0),0),0)"

ActiveCell.Offset(0, 3).FormulaArray = _
"=SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth= R4C2,IF(TMonth<=R5C2,IF(SalRef=""S"",Rev,0),0),0), 0),0),0)-SUM(IF(TPhyLoc=R4C4,IF(TArl=RC1&R4C5,IF(TMonth=R4 C2,IF(TMonth<=R5C2,IF(SalRef=""R"",Rev,0),0),0),0) ,0),0)"

End If

Any Help?