Problem with the "Or" operand in Excel

Prev: restrict a product eg 10*5=40 because 40 is maximum
Next: Auto_Open over Private Sub Workbook_Open()

From: Per Jessen on 29 May 2010 16:33

---

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: Dave Peterson on 29 May 2010 22:43

---

Sometimes, you can use a different branching instruction instead of if:

do until...
select case lcase(cells(startrow,3).value)
case is = "fred", "john", "mary"
rows(startrow).hidden = true
case else
rows(startrow).hidden = false
end select

PPL wrote:
>
> 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

From: PPL on 30 May 2010 10:58

---

Cool,
Thanks for that Dave
Phil
"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4C01D0D4.D22B47A4(a)verizonXSPAM.net...
> Sometimes, you can use a different branching instruction instead of if:
>
> do until...
> select case lcase(cells(startrow,3).value)
> case is = "fred", "john", "mary"
> rows(startrow).hidden = true
> case else
> rows(startrow).hidden = false
> end select
>
> PPL wrote:
>>
>> 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

From: PPL on 30 May 2010 11:12

---

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: Dave Peterson on 30 May 2010 11:27

---

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  |  Next  |  Last
Pages: 1 2 3
Prev: restrict a product eg 10*5=40 because 40 is maximum
Next: Auto_Open over Private Sub Workbook_Open()