Problem with the "Or" operand in Excel
in [Programming]
|
Prev: restrict a product eg 10*5=40 because 40 is maximum
Next: Auto_Open over Private Sub Workbook_Open()
From: BillyBob on 30 May 2010 12:09 PPL, You are not understanding the logic of the OR and AND operators. Each individual expression it evaluated to TRUE or FALSE independent of the other expressions. Logic: OR Operator *Only one* expression must evaluate to TRUE for it to be TRUE AND Operator *All* the expressions must evaluate to TRUE for it to be TRUE Let's look at your original if statement : If Cells(startrow, 3).Value <> "Fred" _ Or Cells(startrow, 3).Value <> "John" _ Or Cells(startrow, 3).Value <> "Mary" Then Cells(startrow, 3).Select Selection.EntireRow.Hidden = True End If It is interpreted as: If the value in the indicated cell is NOT "Fred" OR the value in the indicated cell is NOT "John" OR the value in the indicated cell is NOT "Mary" then hide the selected row This will always evaluate to TRUE because the value in the cell will always NOT be one of those. If the value is "Fred" then it is TRUE that it is not "John" or "Mary". If the value is "John" then it TRUE that it is not "Fred" or "Mary". If the value is "Mary" then it is TRUE it is not "John" or "Fred". If any value is TRUE then the If statement evaluates to TRUE and will hide the row. So as you can see, it is ALWAYS TRUE that it will not be Fred, John or Mary because the value in the cell cannot be all those names at the same time. Let's look at Don Guillett's solution: He kept the OR operator and changed the <> to = If Cells(startrow, 3).Value = "Fred" _ Or Cells(startrow, 3).Value = "John" _ Or Cells(startrow, 3).Value = "Mary" Then Cells(startrow, 3).Select Selection.EntireRow.Hidden = True End If If the value is "Fred" then it is TRUE. If the value is "John" then is TRUE. If the value is "Mary" then it is TRUE. If the value is not Fred, John or Mary, then none of the expressions are TRUE, so the If evaluates to FALSE and will not hide the row. It only hides the row if any expression is TRUE and the only way this can happen is if Fred OR John OR Mary is the cell value. Let's look at Per Jessen's solution: He changed the OR to AND and kept the <> If Cells(startrow, 3).Value <> "Fred" _ AND Cells(startrow, 3).Value <> "John" _ AND Cells(startrow, 3).Value <> "Mary" Then Cells(startrow, 3).Select Selection.EntireRow.Hidden = True End If If the value is "Fred" then it is FALSE that it is not Fred. If the value is "John" then is FALSE that it is not John. If the value is "Mary" then it is FALSE that it is not Mary. If the value is not Fred, John or Mary, then it is TRUE that it is not Fred AND it is TRUE that it is not John AND it is TRUE that it is not Mary. So, if the value is NOT Fred, John or Mary, then the If evaluates to TRUE because ALL the expressions are TRUE and will hide the row. It only hides the row if ALL expressions are TRUE. Hopefully you can see how these logical operators are evaluated. I would suggest opening Visual Basic --> Help and searching "Logical Operators" (without the quotes). BB "PPL" <pp1(a)shawRemoveThis.ca> wrote in message news:wnuMn.83126$gv4.80977(a)newsfe09.iad... > Thanks for repying Per, I appreciate it > So here's my problem, let me step through the logic: > foucus is placed on each cell > The contents is examined > The "If" conditional is excercised to differentiate TRUE from FALSE > against defined arguments > In using the AND operand, we are going test the cell to see if it contains > all three names: Fred AND John AND Mary. If it does then the condition is > TRUE > Well that's not what we want:. The cell contains one name and one name > only which may or may not be Fred, John or Mary, so therefore we should > use the OR operand. > > Clearly my logic is unsound becasue it doesn't work that way. > Hope this makes sense ... > TIA > > Phil > > > > "Per Jessen" <per.jessen(a)mail.dk> wrote in message > news:ey4Vf42$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >> Phil, >> >> Thanks for your reply. >> >> Let's look at the logic. All parts of my statement (using AND) has to be >> true for the entire If statement to evaluate true. >> >> Using OR will return true if just one part of the statement is true. >> >> Hopes this helps. >> ... >> Per >> >> "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >> news:4_cMn.25025$mi.5012(a)newsfe01.iad... >>> Hi Per, >>> Thank you so much for this - it works, and sorry for the confusion in >>> my earlier post, I am actually trying to hide all rows apart from those >>> that contain Fred, or John or Mary >>> >>> You have used the "<>" plus the "And" operand and it works perfectly >>> thank you. Problem is I don't understand why. In my (obviously wrong) >>> logic the Or operand should have done the job - can you explain why it >>> doesn't. Again in my twisted logic I'd have said that inorder for the >>> "If" statement to work with the "And" operand then the cell being tested >>> would have to contain ALL three names! >>> >>> Thank you again for your help. Much appreciated! >>> >>> Phil >>> >>> >>> >>> "Per Jessen" <per.jessen(a)mail.dk> wrote in message >>> news:eCAwkP1$KHA.4308(a)TK2MSFTNGP04.phx.gbl... >>>> Hi >>>> >>>> This will hide all rows which do not contain one of the names. >>>> >>>> Sub MyHideRows() >>>> Dim StartRow As Integer >>>> StartRow = 1 >>>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>> If Cells(StartRow, 3).Value <> "Fred" _ >>>> And Cells(StartRow, 3).Value <> "John" _ >>>> And Cells(StartRow, 3).Value <> "Mary" Then >>>> >>>> Rows(StartRow).Hidden = True >>>> End If >>>> >>>> StartRow = StartRow + 1 >>>> Loop >>>> End Sub >>>> >>>> And this will hide rows which contain one of the selected names: >>>> >>>> Sub MyHideRows() >>>> Dim StartRow As Integer >>>> StartRow = 1 >>>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>> If Cells(StartRow, 3).Value = "Fred" _ >>>> Or Cells(StartRow, 3).Value = "John" _ >>>> Or Cells(StartRow, 3).Value = "Mary" Then >>>> >>>> Rows(StartRow).Hidden = True >>>> End If >>>> >>>> StartRow = StartRow + 1 >>>> Loop >>>> End Sub >>>> >>>> Regards, >>>> Per >>>> >>>> "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >>>> news:9MbMn.19344$yx.12498(a)newsfe13.iad... >>>>> Excel 2002/3 >>>>> I am trying to write a short script that looks at all cells in column >>>>> C and if the cell contains a selected name (i.e., Fred or John or >>>>> Mary) then the entire row is hidden >>>>> I would have thought that the following would have doen it? >>>>> >>>>> Sub MyHideRows() >>>>> Dim startrow As Integer >>>>> startrow = 1 >>>>> Do Until startrow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>>> If Cells(startrow, 3).Value <> "Fred" _ >>>>> Or Cells(startrow, 3).Value <> "John" _ >>>>> Or Cells(startrow, 3).Value <> "Mary" Then >>>>> >>>>> Cells(startrow, 3).Select >>>>> Selection.EntireRow.Hidden = True >>>>> End If >>>>> >>>>> startrow = startrow + 1 >>>>> Loop >>>>> End Sub >>>>> >>>>> The result is that regardless of content the script hides all rows >>>>> >>>>> Interstingly if I strip out the "Or" operands and leave the basic "If" >>>>> statement it works fine. >>>>> So this works: >>>>> >>>>> If Cells(startrow, 3).Value <> "Fred" Then >>>>> Cells(startrow, 3).Select >>>>> Selection.EntireRow.Hidden = True >>>>> End If >>>>> >>>>> Can somebody please please help me with the logic here (or my lack of >>>>> same!) >>>>> >>>>> TIA >>>>> >>>>> Phil >>>>> >>>>> >>>>> >>>>> >>>>> >>> >>> > >
From: PPL on 31 May 2010 08:49 Hi BillyBob, That's really really helpful. Thank you. I have a much better insight into how it works Thanks again Phil "BillyBob" <billybob(a)has.onetooth> wrote in message news:76102ADB-9ADC-4788-B3E9-F4001F71E481(a)microsoft.com... > PPL, > > You are not understanding the logic of the OR and AND operators. Each > individual expression it evaluated to TRUE or FALSE independent of the > other expressions. > > Logic: > > OR Operator > *Only one* expression must evaluate to TRUE for it to be TRUE > > AND Operator > *All* the expressions must evaluate to TRUE for it to be TRUE > > Let's look at your original if statement : > > If Cells(startrow, 3).Value <> "Fred" _ > Or Cells(startrow, 3).Value <> "John" _ > Or Cells(startrow, 3).Value <> "Mary" Then > Cells(startrow, 3).Select > Selection.EntireRow.Hidden = True > End If > > It is interpreted as: > > If the value in the indicated cell is NOT "Fred" OR > the value in the indicated cell is NOT "John" OR > the value in the indicated cell is NOT "Mary" then > hide the selected row > > This will always evaluate to TRUE because the value in the cell will > always NOT be one of those. If the value is "Fred" then it is TRUE that > it is not "John" or "Mary". If the value is "John" then it TRUE that it is > not "Fred" or "Mary". If the value is "Mary" then it is TRUE it is not > "John" or "Fred". If any value is TRUE then the If statement evaluates to > TRUE and will hide the row. So as you can see, it is ALWAYS TRUE that it > will not be Fred, John or Mary because the value in the cell cannot be all > those names at the same time. > > Let's look at Don Guillett's solution: > He kept the OR operator and changed the <> to = > > If Cells(startrow, 3).Value = "Fred" _ > Or Cells(startrow, 3).Value = "John" _ > Or Cells(startrow, 3).Value = "Mary" Then > Cells(startrow, 3).Select > Selection.EntireRow.Hidden = True > End If > > If the value is "Fred" then it is TRUE. If the value is "John" then is > TRUE. If the value is "Mary" then it is TRUE. If the value is not Fred, > John or Mary, then none of the expressions are TRUE, so the If evaluates > to FALSE and will not hide the row. It only hides the row if any > expression is TRUE and the only way this can happen is if Fred OR John OR > Mary is the cell value. > > Let's look at Per Jessen's solution: > He changed the OR to AND and kept the <> > > If Cells(startrow, 3).Value <> "Fred" _ > AND Cells(startrow, 3).Value <> "John" _ > AND Cells(startrow, 3).Value <> "Mary" Then > Cells(startrow, 3).Select > Selection.EntireRow.Hidden = True > End If > > If the value is "Fred" then it is FALSE that it is not Fred. If the value > is "John" then is FALSE that it is not John. If the value is "Mary" then > it is FALSE that it is not Mary. If the value is not Fred, John or Mary, > then it is TRUE that it is not Fred AND it is TRUE that it is not John AND > it is TRUE that it is not Mary. So, if the value is NOT Fred, John or > Mary, then the If evaluates to TRUE because ALL the expressions are TRUE > and will hide the row. It only hides the row if ALL expressions are TRUE. > > Hopefully you can see how these logical operators are evaluated. I would > suggest opening Visual Basic --> Help and searching "Logical Operators" > (without the quotes). > > BB > > > > > > > > > > "PPL" <pp1(a)shawRemoveThis.ca> wrote in message > news:wnuMn.83126$gv4.80977(a)newsfe09.iad... >> Thanks for repying Per, I appreciate it >> So here's my problem, let me step through the logic: >> foucus is placed on each cell >> The contents is examined >> The "If" conditional is excercised to differentiate TRUE from FALSE >> against defined arguments >> In using the AND operand, we are going test the cell to see if it >> contains all three names: Fred AND John AND Mary. If it does then the >> condition is TRUE >> Well that's not what we want:. The cell contains one name and one name >> only which may or may not be Fred, John or Mary, so therefore we should >> use the OR operand. >> >> Clearly my logic is unsound becasue it doesn't work that way. >> Hope this makes sense ... >> TIA >> >> Phil >> >> >> >> "Per Jessen" <per.jessen(a)mail.dk> wrote in message >> news:ey4Vf42$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >>> Phil, >>> >>> Thanks for your reply. >>> >>> Let's look at the logic. All parts of my statement (using AND) has to >>> be true for the entire If statement to evaluate true. >>> >>> Using OR will return true if just one part of the statement is true. >>> >>> Hopes this helps. >>> ... >>> Per >>> >>> "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >>> news:4_cMn.25025$mi.5012(a)newsfe01.iad... >>>> Hi Per, >>>> Thank you so much for this - it works, and sorry for the confusion in >>>> my earlier post, I am actually trying to hide all rows apart from those >>>> that contain Fred, or John or Mary >>>> >>>> You have used the "<>" plus the "And" operand and it works perfectly >>>> thank you. Problem is I don't understand why. In my (obviously wrong) >>>> logic the Or operand should have done the job - can you explain why it >>>> doesn't. Again in my twisted logic I'd have said that inorder for the >>>> "If" statement to work with the "And" operand then the cell being >>>> tested would have to contain ALL three names! >>>> >>>> Thank you again for your help. Much appreciated! >>>> >>>> Phil >>>> >>>> >>>> >>>> "Per Jessen" <per.jessen(a)mail.dk> wrote in message >>>> news:eCAwkP1$KHA.4308(a)TK2MSFTNGP04.phx.gbl... >>>>> Hi >>>>> >>>>> This will hide all rows which do not contain one of the names. >>>>> >>>>> Sub MyHideRows() >>>>> Dim StartRow As Integer >>>>> StartRow = 1 >>>>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>>> If Cells(StartRow, 3).Value <> "Fred" _ >>>>> And Cells(StartRow, 3).Value <> "John" _ >>>>> And Cells(StartRow, 3).Value <> "Mary" Then >>>>> >>>>> Rows(StartRow).Hidden = True >>>>> End If >>>>> >>>>> StartRow = StartRow + 1 >>>>> Loop >>>>> End Sub >>>>> >>>>> And this will hide rows which contain one of the selected names: >>>>> >>>>> Sub MyHideRows() >>>>> Dim StartRow As Integer >>>>> StartRow = 1 >>>>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>>> If Cells(StartRow, 3).Value = "Fred" _ >>>>> Or Cells(StartRow, 3).Value = "John" _ >>>>> Or Cells(StartRow, 3).Value = "Mary" Then >>>>> >>>>> Rows(StartRow).Hidden = True >>>>> End If >>>>> >>>>> StartRow = StartRow + 1 >>>>> Loop >>>>> End Sub >>>>> >>>>> Regards, >>>>> Per >>>>> >>>>> "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >>>>> news:9MbMn.19344$yx.12498(a)newsfe13.iad... >>>>>> Excel 2002/3 >>>>>> I am trying to write a short script that looks at all cells in >>>>>> column C and if the cell contains a selected name (i.e., Fred or John >>>>>> or Mary) then the entire row is hidden >>>>>> I would have thought that the following would have doen it? >>>>>> >>>>>> Sub MyHideRows() >>>>>> Dim startrow As Integer >>>>>> startrow = 1 >>>>>> Do Until startrow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >>>>>> If Cells(startrow, 3).Value <> "Fred" _ >>>>>> Or Cells(startrow, 3).Value <> "John" _ >>>>>> Or Cells(startrow, 3).Value <> "Mary" Then >>>>>> >>>>>> Cells(startrow, 3).Select >>>>>> Selection.EntireRow.Hidden = True >>>>>> End If >>>>>> >>>>>> startrow = startrow + 1 >>>>>> Loop >>>>>> End Sub >>>>>> >>>>>> The result is that regardless of content the script hides all rows >>>>>> >>>>>> Interstingly if I strip out the "Or" operands and leave the basic >>>>>> "If" statement it works fine. >>>>>> So this works: >>>>>> >>>>>> If Cells(startrow, 3).Value <> "Fred" Then >>>>>> Cells(startrow, 3).Select >>>>>> Selection.EntireRow.Hidden = True >>>>>> End If >>>>>> >>>>>> Can somebody please please help me with the logic here (or my lack of >>>>>> same!) >>>>>> >>>>>> TIA >>>>>> >>>>>> Phil >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> >> >> >
From: PPL on 31 May 2010 08:50 Thanks Dave, Your's together with BillyBob's reply has really helped m to understand this stuff. Much appreciated Thanks again Phil "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4C0283FA.362FEC7E(a)verizonXSPAM.net... > > I have that same problem with the And/or =,<> stuff. > > So I try to make it easy for me. I'll change my then/elses around to do > what I > want: > > if cell.value = "john" _ > or cell.value = "fred" _ > or cell.value = "mary" then > 'do what needs to be done if it's one of these > else > 'do what needs to be done if it's not one of these. > end if > > Sometimes, my code will look like: > > if cell.value = "john" _ > or cell.value = "fred" _ > or cell.value = "mary" then > 'do nothing > else > cell.offset(0,1).value = "not john, fred or mary" > end if > > > > PPL wrote: >> >> Thanks for repying Per, I appreciate it >> So here's my problem, let me step through the logic: >> foucus is placed on each cell >> The contents is examined >> The "If" conditional is excercised to differentiate TRUE from FALSE >> against >> defined arguments >> In using the AND operand, we are going test the cell to see if it >> contains >> all three names: Fred AND John AND Mary. If it does then the condition is >> TRUE >> Well that's not what we want:. The cell contains one name and one name >> only >> which may or may not be Fred, John or Mary, so therefore we should use >> the >> OR operand. >> >> Clearly my logic is unsound becasue it doesn't work that way. >> Hope this makes sense ... >> TIA >> >> Phil >> >> "Per Jessen" <per.jessen(a)mail.dk> wrote in message >> news:ey4Vf42$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >> > Phil, >> > >> > Thanks for your reply. >> > >> > Let's look at the logic. All parts of my statement (using AND) has to >> > be >> > true for the entire If statement to evaluate true. >> > >> > Using OR will return true if just one part of the statement is true. >> > >> > Hopes this helps. >> > ... >> > Per >> > >> > "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >> > news:4_cMn.25025$mi.5012(a)newsfe01.iad... >> >> Hi Per, >> >> Thank you so much for this - it works, and sorry for the confusion in >> >> my >> >> earlier post, I am actually trying to hide all rows apart from those >> >> that >> >> contain Fred, or John or Mary >> >> >> >> You have used the "<>" plus the "And" operand and it works perfectly >> >> thank you. Problem is I don't understand why. In my (obviously wrong) >> >> logic the Or operand should have done the job - can you explain why it >> >> doesn't. Again in my twisted logic I'd have said that inorder for the >> >> "If" statement to work with the "And" operand then the cell being >> >> tested >> >> would have to contain ALL three names! >> >> >> >> Thank you again for your help. Much appreciated! >> >> >> >> Phil >> >> >> >> >> >> >> >> "Per Jessen" <per.jessen(a)mail.dk> wrote in message >> >> news:eCAwkP1$KHA.4308(a)TK2MSFTNGP04.phx.gbl... >> >>> Hi >> >>> >> >>> This will hide all rows which do not contain one of the names. >> >>> >> >>> Sub MyHideRows() >> >>> Dim StartRow As Integer >> >>> StartRow = 1 >> >>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >> >>> If Cells(StartRow, 3).Value <> "Fred" _ >> >>> And Cells(StartRow, 3).Value <> "John" _ >> >>> And Cells(StartRow, 3).Value <> "Mary" Then >> >>> >> >>> Rows(StartRow).Hidden = True >> >>> End If >> >>> >> >>> StartRow = StartRow + 1 >> >>> Loop >> >>> End Sub >> >>> >> >>> And this will hide rows which contain one of the selected names: >> >>> >> >>> Sub MyHideRows() >> >>> Dim StartRow As Integer >> >>> StartRow = 1 >> >>> Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >> >>> If Cells(StartRow, 3).Value = "Fred" _ >> >>> Or Cells(StartRow, 3).Value = "John" _ >> >>> Or Cells(StartRow, 3).Value = "Mary" Then >> >>> >> >>> Rows(StartRow).Hidden = True >> >>> End If >> >>> >> >>> StartRow = StartRow + 1 >> >>> Loop >> >>> End Sub >> >>> >> >>> Regards, >> >>> Per >> >>> >> >>> "PPL" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen >> >>> news:9MbMn.19344$yx.12498(a)newsfe13.iad... >> >>>> Excel 2002/3 >> >>>> I am trying to write a short script that looks at all cells in >> >>>> column >> >>>> C and if the cell contains a selected name (i.e., Fred or John or >> >>>> Mary) >> >>>> then the entire row is hidden >> >>>> I would have thought that the following would have doen it? >> >>>> >> >>>> Sub MyHideRows() >> >>>> Dim startrow As Integer >> >>>> startrow = 1 >> >>>> Do Until startrow > Cells(Cells.Rows.Count, "C").End(xlUp).Row >> >>>> If Cells(startrow, 3).Value <> "Fred" _ >> >>>> Or Cells(startrow, 3).Value <> "John" _ >> >>>> Or Cells(startrow, 3).Value <> "Mary" Then >> >>>> >> >>>> Cells(startrow, 3).Select >> >>>> Selection.EntireRow.Hidden = True >> >>>> End If >> >>>> >> >>>> startrow = startrow + 1 >> >>>> Loop >> >>>> End Sub >> >>>> >> >>>> The result is that regardless of content the script hides all rows >> >>>> >> >>>> Interstingly if I strip out the "Or" operands and leave the basic >> >>>> "If" >> >>>> statement it works fine. >> >>>> So this works: >> >>>> >> >>>> If Cells(startrow, 3).Value <> "Fred" Then >> >>>> Cells(startrow, 3).Select >> >>>> Selection.EntireRow.Hidden = True >> >>>> End If >> >>>> >> >>>> Can somebody please please help me with the logic here (or my lack >> >>>> of >> >>>> same!) >> >>>> >> >>>> TIA >> >>>> >> >>>> Phil >> >>>> >> >>>> >> >>>> >> >>>> >> >>>> >> >> >> >> > > -- > > Dave Peterson
First
|
Prev
|
Pages: 1 2 3 Prev: restrict a product eg 10*5=40 because 40 is maximum Next: Auto_Open over Private Sub Workbook_Open() |