Problem with the "Or" operand in Excel

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





0
PPL
5/29/2010 5:01:51 PM
excel.programming 6508 articles. 2 followers. Follow

12 Replies
786 Views

Similar Articles

[PageSpeed] 55

You were saying <> which is NOT equal instead of = Also, work from the 
bottom up

Sub MyHideRowsSAS()
Rows.Hidden = False
For i = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If LCase(Cells(i, 3)) = "fred" Or _
   LCase(Cells(i, 3)) = "john" Or _
   LCase(Cells(i, 3)) = "mary" Then Rows(i).Hidden = True
Next i
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"PPL" <pp1@shawRemoveThis.ca> wrote in message 
news:9MbMn.19344$yx.12498@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
>
>
>
>
> 

0
Don
5/29/2010 5:17:33 PM
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@shawRemoveThis.ca> skrev i meddelelsen 
news:9MbMn.19344$yx.12498@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
>
>
>
>
> 
0
Per
5/29/2010 5:25:16 PM
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@mail.dk> wrote in message 
news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen 
> news:9MbMn.19344$yx.12498@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
>>
>>
>>
>>
>> 


0
PPL
5/29/2010 6:24:59 PM
Hi Don,
Thanks for this,
My mistake for the confusion. I'm trying to hide all rows that do not 
contain one of the three names (Fred or John or Mary)
Why does working from the bottom up make a difference?

Thanks again
Phil


"Don Guillett" <dguillett1@gmail.com> wrote in message 
news:e8gRWL1$KHA.1892@TK2MSFTNGP05.phx.gbl...
> You were saying <> which is NOT equal instead of = Also, work from the 
> bottom up
>
> Sub MyHideRowsSAS()
> Rows.Hidden = False
> For i = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
> If LCase(Cells(i, 3)) = "fred" Or _
>   LCase(Cells(i, 3)) = "john" Or _
>   LCase(Cells(i, 3)) = "mary" Then Rows(i).Hidden = True
> Next i
> End Sub
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "PPL" <pp1@shawRemoveThis.ca> wrote in message 
> news:9MbMn.19344$yx.12498@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
>>
>>
>>
>>
>>
> 


0
PPL
5/29/2010 6:33:57 PM
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@shawRemoveThis.ca> skrev i meddelelsen 
news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message 
> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen 
>> news:9MbMn.19344$yx.12498@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
>>>
>>>
>>>
>>>
>>>
>
> 
0
Per
5/29/2010 8:33:01 PM
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
0
Dave
5/30/2010 2:43:32 AM
Cool,
Thanks for that Dave
Phil
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4C01D0D4.D22B47A4@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 


0
PPL
5/30/2010 2:01:25 PM
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@mail.dk> wrote in message 
news:ey4Vf42$KHA.5044@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@shawRemoveThis.ca> skrev i meddelelsen 
> news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message 
>> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen 
>>> news:9MbMn.19344$yx.12498@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
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>> 


0
PPL
5/30/2010 2:15:26 PM
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@mail.dk> wrote in message
> news:ey4Vf42$KHA.5044@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@shawRemoveThis.ca> skrev i meddelelsen
> > news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message
> >> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen
> >>> news:9MbMn.19344$yx.12498@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
0
Dave
5/30/2010 3:27:54 PM
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@shawRemoveThis.ca> wrote in message 
news:wnuMn.83126$gv4.80977@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@mail.dk> wrote in message 
> news:ey4Vf42$KHA.5044@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@shawRemoveThis.ca> skrev i meddelelsen 
>> news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message 
>>> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen 
>>>> news:9MbMn.19344$yx.12498@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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>
> 

0
BillyBob
5/30/2010 4:09:40 PM
Hi BillyBob,
That's really really helpful.
Thank you.
I have a much better insight into how it works
Thanks again

Phil

"BillyBob" <billybob@has.onetooth> wrote in message 
news:76102ADB-9ADC-4788-B3E9-F4001F71E481@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@shawRemoveThis.ca> wrote in message 
> news:wnuMn.83126$gv4.80977@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@mail.dk> wrote in message 
>> news:ey4Vf42$KHA.5044@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@shawRemoveThis.ca> skrev i meddelelsen 
>>> news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message 
>>>> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen 
>>>>> news:9MbMn.19344$yx.12498@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
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
> 


0
PPL
5/31/2010 11:51:32 AM
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@verizonXSPAM.net> wrote in message 
news:4C0283FA.362FEC7E@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@mail.dk> wrote in message
>> news:ey4Vf42$KHA.5044@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@shawRemoveThis.ca> skrev i meddelelsen
>> > news:4_cMn.25025$mi.5012@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@mail.dk> wrote in message
>> >> news:eCAwkP1$KHA.4308@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@shawRemoveThis.ca> skrev i meddelelsen
>> >>> news:9MbMn.19344$yx.12498@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 


0
PPL
5/31/2010 11:55:21 AM
Reply:

Similar Artilces:

List of DDE commands for Excel
Hi, I wont to send some commands from my application to Excel via DDE. Fo example : to select specific sheet in the excel workbook I use nex command - [WORKBOOK.SELECT("sheet-name")]. But I can't find the list of all these commands. Please help me -- Message posted from http://www.ExcelForum.com If you have a look in the object browser in the excel visual basi editor it will have a list of the VB commands excel will accept and i you hit F1 on them it will tell you what they do and usually giv syntax. Dunca -- Message posted from http://www.ExcelForum.com 2 DNF Karran Th...

Problem shortcuts in Word
Hello, I work with Word 2008. In the beginning there is no problem, but suddenly i have problem with shortcuts. When i want to do "cmd c" it make an other shortcuts same thing for "cmd s", "cmd v" and surly some other. But when i write there is no problem "c" is "c"... If somebody have an idea please help me. Thank you. Unless you indicate your specific update level of Office as well as OS X & describe what the keystrokes *are* doing contrary to what you expect there isn't much we can offer. -- Regards |:>)...

encoding problem in Outlook 2007 importing Outlook 2000 personal folders
I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese characters in the subject became unreadable but it's ok to display in message body. In Outlook 2000, there's no problem to display the Chinese characters in the subject. What's wrong with Outlook 2007? What setttings should I change to fix the problem? Thanks. cyl <u8526505@gmail.com> wrote: > I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese > characters in the subject became unreadable but it's ok to display in > message body. In Outlook 2000, there's no problem t...

format of emailed excel file
In excel 2003 when I email a a particulat worksheet in a workbook I lose all the formating when it is recieved in Outlook 2003. Is there anyway to preserve the excel "look"? Thnaks, Craig S ...

Resource editor problems
Hello all, Under VC++ 7.1... Please consider these two lines: IDC_ARROW_ADD_CANCEL DISCARDABLE "res\\cur00004.cur" IDC_ARROW_ADD_CANCEL CURSOR DISCARDABLE "res\\cur00004.cur" The second is a hand-edited change to the first. Both will compile just fine in the resource compiler. However, after I use a text editor to make this change, I cannot open the .rc file in the resource editor. The resource editor issues the following error open attempting to open the .rc file: error RC2135 : file not found: CURSOR Can anyone shed light on what's going on here? Thanks, Dave ...

Directory Replication Problem #2
Hi, recently my Exchange Server directory database receives changes from other servers but does not send out its own changes. Check from the knowledge base, to correct the USN discrepancy need to use Authrest.exe (need to amke changes for about 100+ users). Does anybody know where to get this file 'Authrest.exe' for exchange server 5.5? Exchange server 5.5 CD only provide this file for ver.4.0. Have anyone use it before? Regards, "Sharon Tan" <sharon_tansk@yahoo.com.sg> wrote: >Hi, recently my Exchange Server directory database >receives changes from ot...

I want work, and view,a single sheet in Mac Excel ... like I did in PC Excel
Operating System: Mac OS X 10.6 (Snow Leopard) How do you setup MAC Excel to be able to work in a single sheet format ... the Mac Excel comes up showing several sheets. It isn't showing "several sheets", it's using the newly introduced Page Layout View by default. You're only viewing the one sheet but it's 'separated' into pages as it would be if you were to print the sheet. You can click the Normal View button in the lower left corner of the document window or go to View> Normal to change the view of any given sheet at any time you wish � Norma...

Software of scripting to grab data from MS word file and fill in Excel fields?
I have a 90 page financial report from MS word that I get each month. I would like to find a software package or scripting tool that will allow me to automate the process of grabbing #s from the ms word file and filling in fields in an excel file. I have a feel that there may be quite of programming involved in doing this. But perhaps there are some more efficient solutions out there. I would appreciate any advice. Thanks Hi, Do the numbers appear in the same part of the word document each tim (eg always in the same row/column of the same table). If so, copyin the data into excel should be ...

Cell Format
I have a spreadsheet with cells that I'm trying to type the date into. I type in the date exactly like this: 09/26/03. When I hit "enter" MS Excel displays "37890" in the cell. No matter what I do I can't the cell to show: 09/26/03. I think that somehow I need to "strip" the cell of what it's original format is, but I can't figure out how. I try to use the Date option in the format cell screen but it doesn't work. Curtis I tried this and it didn't work. I also tried the "Text to columns" feature. No success. >...

User Defined Variables in MS query cause a problem to import data
If I use user defined variables in MS query to import data into excel sheet, the query executes but no data gets imported into the excel sheet. If I get rid of the variable the results can be imported into the Excel sheet. ...

Excel formulas #3
I am using a workday fomula to fill in dates across my spreadsheet. The formula also removes holidays. I need to include Saturdays as a workday. How can I accomplish this? =B1+SMALL(IF((WEEKDAY(B1+(ROW(INDIRECT("1:"&10))))={2,3,4,5,6,7})* ISNA(MATCH(B1+(ROW(INDIRECT("1:"&10))),holidays,0)),ROW(INDIRECT("1:"&10))),1) this is an arry formula, so commit with Ctrl-Shift-Enter -- __________________________________ HTH Bob "aviationmech" <aviationmech@discussions.microsoft.com> wrote in message news:D014DA1C-4B9F-4285-8C28-C804362521...

Problem after sorting
In my Excel 2007 workbook I have two sheets The "Master Names" sheet has columns: (A) First|(B) Last (C) First (B) Last (concatenated) The "Selected Names" sheet contains (A) cells which link to selected (C) First Last (concatenated) cells in the Master Names sheet. Everything links and displays fine except when I add names to the bottom of the Master Names sheet and then sort using (B) Last name column. When I do this I get a 0 (zero) in the (A) First Second cells in the Selected names sheet and other cells in this sheet have the wrong name. Obviously I am doin...

OL 2003 registry or installn problem
Outlook 2003 (part of office 2003 suite) opens and forgets password for email accounts. Tried to open tools-email account and I got operation failed due to a registry or installation problem. also can' view Outlook store location, can't open property of folder w/o getting error message tried restart in control panel-mail, can't see any account for any of the profiles. tried restoring form office settings and failed tried repair and no better result what else can I do other than total uninstall / re-install and still keep the app't, tasks, reminders for the m...

CD Autorun problem
Hi! I need one information on Urgent basis. We are producing one CD .It should be autorun and run into W98 laptop. We have written AutoRun.inf accordingly and put into CD already. But the problem is that the by default our laptop's auto run is disabled. By somehow I need solution for enabling the autorun feature in property or registry level. And it will be good enough for me to get the information on windows programming level if there is any windows32 dll or MFC which can do this job for enabling the autorun feature, so that when CD is inserted autorun should work. If anybody can help...

how do I add error bars to a 3D chart in excel?
The help states you can only add error bars to data series in 2D area. Is there a way to add them to a 3D chart? Hi, I would not have thought so. Obviously as it is not a built-in option the only way would be a work around perhaps using dummy series. Unfortunately you can create 3d combination charts. Stick with the 2d view. Cheers Andy elahe wrote: > The help states you can only add error bars to data series in 2D area. Is > there a way to add them to a 3D chart? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info I checked, and error bars are not offered for 3D ch...

Word/Excel: Freezing on Save
I have a client using Office 2001 on a B/W G3, OS 8.6, and every time she tries to save a new Word or Excel file, the system hangs. More specifically, it's not truly hanging -- the cursor moves but it's stuck showing the wrist watch. The system *does* hang when you try to force-quit the application. Short of wiping the entire hard drive and reinstalling everything, she has tried about everything else... Norton says the drive is OK. She's has uninstalled/reinstalled Office countless times. All to no avail. TIA for any help you can provide. Brian Hi System hangs are usually the...

Exchange Explorer problem
I can't get access at all to Exchange Explorer I get the below error after I say yes to the message 'This page is accessing information that is not under it's control. This poses a security risk. Do you want to continue.' 'Server not available or a unrecoverable error occurred" I can't get into exchange explorer at all, I've tried administrator accounts as the username and pass and also accounts like exmerge, I've tried going to just server/exchange and exchange/mailbox using the mailbox I'm trying to get to's username and password. I recen...

Money Budget and Mortgage problems HELP!
I set up a budget with my mortgage payment. The problem is, th mortgage payment is acutally showing up on Special when I view th budget and I can't budget anything under special. How do I get th budget to recognize my mortgage so that I don't have to line items i budget one with zero target (special debt) and one with zero expense (debt loan) I am having the same problem. If you or anyone else figures out the answer, please let me know at chelly(remove this)@gci.net. THANK YOU! "marzomarz" wrote: > I set up a budget with my mortgage payment. The problem is, the &...

Access 2003 Form Wizard and Toggle Button Confusion/Problems
I am trying to use the Access 2003 Form Wizard to create a subform that will response to a toggle button that I created and to display that subform within my main form. However, I do not get the wizard window that is suppose to allow me to indicate that I want this to be a linked form so that it will appear within my main form (I see this example in a book that I have). Nor can I figure out how to manually create a link to that subform so that it is associated to the toggle buttion that I created so that it will appear within my main form. (I figured out how to write simple code so ...

Uninstall Sales for Outlook problem
I had to reinstall the server program because CRM trashed it. Now I have it up and running again but I can't install the Sales for Outlook because there is already an install there. When I try to uninstall it (in add/remove programs) it says that this action is only valid for products that are currently installed. How do I manually uninstall Microsoft CRM Sales for Outlook? I am trying so hard to use this program to see if it will be a benefit to customers but after 4 months on and off of trying to get it working, a am not sure I see the point. -- )))))))))))))))))))))))))))))))))...

Excel 2003 VB referencing #N/A not working but works in Excel 2007
I have a very simple macro that works in Excel 2007 but hangs up constantly in Excel 2003. Does anyone have a work around? Problem: When referencing cells that have a #N/A value, Excel 2003 craps out even if I use the ISNA application function before it. The following 2 statements will cause Excel 2003 VB to stop regardless of order if the cell that is referenced comes up with #N/A. Series = Cells(RowNumStart + X - 1, SeriesCol).Value If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then Further background.... I am actually wanting to find #...

Fix for EXCHANGE to hotmail sending problem bdat
TURN OFF THE AUTO PROTECT FEATURE FOR INTERNET EMAIL IN THE SYMANTEC DESKTOP CLIENT THAT YOU INSTALLED ON YOUR EXCHANGE SERVER. IT MESSES UP BDAT ARGGGG!!!!!!! SOMETIMES I'M SUCH AN IDIOT!!! ...

Excel auto accumulation formulas
Hi hello, I need a formula for auto accumulation formula like example: John need to give me USD50 everyday for one month, so if he never paid the money i will key in 0.00 in that collumn and the ammount accmulated for today is 50.00 and the next day calculation like USD100, but if he did the payment full i will key in 50.00, if he only pays USD30 i will key in 30.00, so the balanse out standing will shows 30.00 for today and will shows 80.00 on the next day. I need a formula for every single day it auto accumulate USD50. I'm trying alot of formula but none is working. Th...

Problem with Data Migration Framework, nothing is migrated....
Hi, I am trying to get some data into CRM with Data Migration Framework, but I have some problems. I have followed the guide, but when I am doing the migrate process the data fails to migrate. To eliminate some issues I have tried to manaully insert a testrecord in the cdf_mscrm database using the SQL enterprise manager. The only data in the database is a single record in the cdf_account table with corresponding records in _ext and _info databases. The fields AccountID, OwningUser and Name has data, the other fields are empty. OwningUser has a value wich corresponds with a SystemUserId in the...

Problem using problem reporting
I noticed that windows 7 error reporting no longer works on my machine. It goes through the motions then reports it cannot connect to the error reporting service at this time, try again later. I have tried many times, same results each day. Any clue as to what might be wrong? Anyone heard of this error or know of a fix I'm using Windows 7 64 bit. Seen it reported a few times. The cause has worked out to one of three: 1.) Third party firewall 2.) Horked DNS 3.) Serious infection. Unfortunately, the third is the most common I've seen. -- Charlie. http://msmvps....