Multiple IF statements

Hi,

I'm having trouble with my IF statements. In my userform I have 2 questions, and I want my rows to be hidden on the basis of these questions. Here is my code:

Please Login or Register  to view this content.

An example of the problem is that if I select Type = Brother and Height = 160, it will show results that are Brother and Sister, as some Sisters may be 160 Height as well. I want it so that it will show only results that are exclusively Brother and 160 Height. Not sure how to achieve this.

Any help is appreciated :]

Hi,

Give this code a try:

Please Login or Register  to view this content.

What it does is determine whether whatever is in the value cells meets your conditions, but when it actually assigns the values it checks to see if that particular category is actually checked off. The inside of the Not() returns False whenever there is a problem..... which the Not() turns into True so it can be hidden.

S

Oh, I guess it could just be put as:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

S

If you have other questions, it's probably best to start another thread on whatever your new topic is each time. That way the rest of us know when a thread is complete. Additionally, different people here have a better grasp of some things than others. Based on the topic, they can more readily determine when they are able to help you and when they can't.
_____________________________________________________________

As to your other question... There are a few differences between IIF() and IF/THEN/ELSE. Someone else can probably give a better explanation, but here's my take on it.

The main difference is IIF() is a function (and is called as such) whereas IF/THEN/ELSE is part of the code structure.

This means IIF() is limited to returning one of two values back to the expression it's called from. However, the benefit is that you can actually put it inside the expression as was done in the code above which in some instances can reduce code complexity.

The above could be done within the IF/THEN/ELSE structure, here's an example of how ... but please note this still utilizes a similar method to the above, just the calculations are done separately. (Not that it couldn't be done completely differently so it's totally nested, but I think it would make the code way too long and cumbersome)

Please Login or Register  to view this content.

S

Your problem when choosing both Brother and Sister -- what it boils down to is in those situations, you should be only checking against the Height. I'd just add some code like the following to eliminate that before you do the test to hide the rows.

Please Login or Register  to view this content.

S

/Edit

Sorry maybe I was not clear. There are also other "types", for example in addition to brother and sister, there are "father" and "mother", under the same column = TYPE_COL

That way I would like sometimes to select Brother and Sister as Types.

At the end, you could probably change it to the following:

Please Login or Register  to view this content.

This would check the Height and only require that one of the other two be valid.

S

Can you post a sample of your spreadsheet?

S

Ok, looking at it, here's what I'd suggest you do.

Note:
The code wasn't thoroughly tested. There may be mistakes within that you'll have to track down.

Suggestion 1:
I'd probably ditch the boxes that appear on the side that populate with the description when you check things off. The text you search for can be just put into the code.

Suggestion 2:
For each of the three Statuses (Married, Employed, Degree) I'd recommend you add an extra box for each. I'll only discuss the Married one in this example, but the other two are similar.

For Married, my recommendation is to add a checkbox for Unmarried. When both boxes are blank, then Marriage isn't something that people want to filter for. Otherwise, when one of them is checked, it is something that must be filtered for. I'll assume that you create a checkbox called CheckUnmarried. then, I'd recommend you set up your code as follows for the Clicks:

Please Login or Register  to view this content.

As stated, the other two (Employed and Degree) are similar.

Suggestion 3:

I'd add the following two Test functions. The first one checks for the relationships. If no relationship is checked, then relationship doesn't matter (ie. Return True). If any relationship is checked, then we want to return True if a selected relationship matches the line, or False otherwise.

The second function can be used with any field where you have two checkboxes (as mentioned in Suggestion 2) where the values in the cells are either "Yes" or "" (that is, blank). Suggestion 4 deals with how to use this function. This part just states it.

Please Login or Register  to view this content.

Suggestion 4:

The cmdOK_Click() function can probably be rewritten as follows. This takes into account the changes suggested by Suggestion 2 and Suggestion 3.

Please Login or Register  to view this content.

S

[This paragraph may be the most useful to you in this response] I'd recommend you put a breakpoint at the beginning of your Click subroutine. Then, when you execute your macro, hit F8 to go through the lines one by one. You can mouse over the variables to see what values they have at a given time. It might help you better understand the program flow.

The below may not explain this to your satisfaction (although I hope it does work). If it doesn't, you really should research into how the Logic of And, Or, Not work. Here's the first site I came across on the net -- I can't vouch for it, it just looked like it discussed the topic. http://visualbasic.about.com/od/usevb6/l/aa032203a.htm

ResultCheckRelationship = CheckRelationshipMatch(r.Cells(, TYPE_COL))

To check the relationship part of the criteria, you want to know if any of the selected relationships match the current row. What this does is create a Function that does this test for you.

The Function CheckRelationshipMatch's whole purpose is to determine whether or not the current row matches your criteria from the checkboxes. As an example, on one pass through it might be called as follows:

ResultCheckRelationship = CheckRelationshipMatch("Brother")

whereas the next time it might be called as follows:

ResultCheckRelationship = CheckRelationshipMatch("Sister")

(CheckBrother.Value And Relationship = "Brother")

Basically, you want the CheckRelationshipMatch function to return TRUE if one of the selected relationships is what is on the Row currently being considered. Please keep in mind that Relationship is the value that is passed from your Click() subroutine and represents the relationship on the current row being considered.

Let's break that line down into the two parts: (1) CheckBrother.Value (2) Relationship = "Brother".

This is good under the following circumstances: (Keep in mind there are 4 parts to this (it's done for Father, Mother, Sister too), and using the Or means that only one of them has to return Good.)

Good:

• CheckBrother.Value = TRUE, Relationship = "Brother" Good because they've checked the box off, and the Relationship is what they want.

Bad:

• CheckBrother.Value = FALSE, Relationship = "Brother" Bad because they didn't check the Brother box off. The relationship doesn't matter. (If one of the other relationships was checked off, then one of those statements will indicate it is a good line.)
• CheckBrother.Value = FALSE, Relationship = "Sister" or "Mother" or "Father" Again, bad because they didn't check the Brother box. If one of the other relationships was checked, one of these will determine whether it is good or bad.
• CheckBrother.Value = TRUE, Relationship = "Sister" or "Mother" or "Father" Again, bad because they didn't check the Brother box. If one of the other relationships was checked, one of these will determine whether it is good or bad.

So basically, one of the above four combinations will be the case each time this expression is evaluated. As it is done once for each of the relationships (Mother, Father, Brother, Sister), you'll get a string of 4 results, each connected by an Or. Only one of these has to be true for the line to be good.

S