if_and_or_then loop help :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

if_and_or_then loop help
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

if_and_or_then loop help

I am trying to add some logic to a particular project I am working on. The form collects data basaed on a users input and then makes decisions based on certain criteria.
I am having some trouble getting a If (condition) and (condition) and (condition) then loop to give me my result. I feel that I am so close but am missing something pretty obvious.
Somebody please help.
The code is reading a excel database for a "true" or "false" input in a series of cells. If all the conditions are fullfilled, it routes the form to a particular user. The problem I am having when I step through the code is that the "and values" are all reading ok, but the "then" result is not tripping the value it should.
Sorry this is so long but I just want people to understand the problem.
code:

code:
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _
'((ActiveCell.Offset(0, 20) = "TRUE" Or _
'ActiveCell.Offset(0, 112) = "TRUE") And _
'(ActiveCell.Offset(0, 27) = "TRUE" Or _
'ActiveCell.Offset(0, 114) = "TRUE") And _
'(ActiveCell.Offset(0, 34) = "TRUE" Or _
'ActiveCell.Offset(0, 116) = "TRUE") And _
'(ActiveCell.Offset(0, 41) = "TRUE" Or _
'ActiveCell.Offset(0, 118) = "TRUE") And _
'(ActiveCell.Offset(0, 48) = "TRUE" Or _
'ActiveCell.Offset(0, 120) = "TRUE") And _
'(ActiveCell.Offset(0, 55) = "TRUE" Or _
'ActiveCell.Offset(0, 122) = "TRUE") And _
'(ActiveCell.Offset(0, 62) = "TRUE" Or _
'ActiveCell.Offset(0, 124) = "TRUE") And _
'(ActiveCell.Offset(0, 83) = "TRUE" Or _
'ActiveCell.Offset(0, 130) = "TRUE")) Then
'checked = True
'Exit Sub



If anyone can help I would appreciate it.

Post Fri Sep 19, 2003 11:58 am 
 View user's profile Send private message Send e-mail

jmiskey
Board Master
Board Master


Joined: 02 Aug 2002
Posts: 3246

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

Here is a slightly different approach you may want to consider. Try creating separate IF statements that add a value to a running total, then check your total, i.e.:

MyTotal=0
If Condition 1 then MyTotal=MyTotal + 1
If Condition 2 then MyTotal=MyTotal + 1
...
If Condition 10 then MyTotal = MyTotal + 1
If MyTotal = 10 then checked = True
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

Post Fri Sep 19, 2003 12:09 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

Another thing ...

Are the cell values Boolean TRUE/FALSE? If so you should omit the quotes.

Post Fri Sep 19, 2003 12:16 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

Are you sure your capitals and smalls are correct - the test will be case sensitive. Fabricate F/G Wheel covers is a long string and your caps and smalls are inconsistent. What you might do is compare Ucase(Range("d9").Value) to "FABRICATE F/G WHEEL COVERS".

Post Fri Sep 19, 2003 12:25 pm 
 View user's profile Send private message

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

The case is ok, because that statement (while stepping throught the loop) show that it is satisfied.

Post Fri Sep 19, 2003 12:27 pm 
 View user's profile Send private message Send e-mail

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

Try Andrew's suggestion too - still it would be a better practice to convert everything to caps when making the comparison.

Post Fri Sep 19, 2003 12:31 pm 
 View user's profile Send private message

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

I will do that as an addition. Thanks.

However, I still have the problem. any help????

Post Fri Sep 19, 2003 12:35 pm 
 View user's profile Send private message Send e-mail

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

Can I suggest that you start with just 2 conditions, eg:

code:

ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _
((ActiveCell.Offset(0, 20) = "TRUE" Or _
ActiveCell.Offset(0, 112) = "TRUE")




and see if that works. If it does then add the next condition and test again.

If it doesn't then try something like this to debug your code:

code:

ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" Then
MsgBox ActiveCell.Offset(0, 20) = "TRUE"
MsgBox ActiveCell.Offset(0, 112) = "TRUE"
MsgBox ((ActiveCell.Offset(0, 20) = "TRUE" Or _
ActiveCell.Offset(0, 112) = "TRUE")
End If




It may be that your offsets are wrong.

Post Fri Sep 19, 2003 12:46 pm 
 View user's profile Send private message

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

quote:
Originally posted by Andrew Poulsom:
Another thing ...

Are the cell values Boolean TRUE/FALSE? If so you should omit the quotes.


I verified and checked the boolean value it is NOT a boolean result itis just a text "TRUE" or "FALSE" result in the cell, so I am keeping the quotes.

Post Fri Sep 19, 2003 12:46 pm 
 View user's profile Send private message Send e-mail

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

quote:
Originally posted by Andrew Poulsom:
Can I suggest that you start with just 2 conditions, eg:

code:

ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _
((ActiveCell.Offset(0, 20) = "TRUE" Or _
ActiveCell.Offset(0, 112) = "TRUE")




and see if that works. If it does then add the next condition and test again.

If it doesn't then try something like this to debug your code:

code:

ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" Then
MsgBox ActiveCell.Offset(0, 20) = "TRUE"
MsgBox ActiveCell.Offset(0, 112) = "TRUE"
MsgBox ((ActiveCell.Offset(0, 20) = "TRUE" Or _
ActiveCell.Offset(0, 112) = "TRUE")
End If




It may be that your offsets are wrong.


Andrew,
When stepping throught the code (hovering over the statement) the values are showing that they are "True" or "false" its just that when the program hits the THEN statement it skips right over it . That is what is so confusing. It looks as if all the conditions are fullfilled but yet it skips over the part I need to happen.

Post Fri Sep 19, 2003 12:50 pm 
 View user's profile Send private message Send e-mail

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

I trust:

'checked = True
'Exit Sub

aren't commented out like they are in your post.

Post Fri Sep 19, 2003 12:53 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

It will skip the THEN if the overall test is false - did you try evaluating each of your conditions to see what they each come out to?

Post Fri Sep 19, 2003 12:57 pm 
 View user's profile Send private message

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

I see. Now when stepping through the entire and loop highlites, each individual case looks like it is satisfied so what would cause the whole case to fail if the individual parts don't?

Post Fri Sep 19, 2003 1:06 pm 
 View user's profile Send private message Send e-mail

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

You have a lot of "ANDed" tests that would have to all be true... did you use shift F9 on each one to see what it evaluated to? If they all come out true, then I'm not sure what to suggest.

Post Fri Sep 19, 2003 1:14 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

One more suggestion - be sure your active cell is what you think it is.

Post Fri Sep 19, 2003 1:15 pm 
 View user's profile Send private message

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

earlyd,

The procedure is being called from another, and I have verified the active cell is what I want. That is what is so frustrating about this I guess. Thanks everyone for helping!!!!!

As I eluded to I have verified the results of the test and they come out.
What I have begun to do now is remove the paranthesis and it seems like it will do the trick. I will post soon and let all know.

Mike

Post Fri Sep 19, 2003 1:20 pm 
 View user's profile Send private message Send e-mail

mswantek
Board Regular


Joined: 10 Jul 2002
Posts: 90

Flag: Blank

Status: Offline

 Reply with quote  

Re: if_and_or_then loop help

For all that care it WAS the parenthesis around the and or statements that caused it to not change the value called by the "then" statement. Thanks for all the other great ideas!!!!! I will incorporate them into the next release of my "program"!!!!

icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif

This issue can now be closed!!!!

Thanks.

Mike

Post Fri Sep 19, 2003 1:25 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.