Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, if statement, maybe an and statement | Excel Discussion (Misc queries) | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |