Multiple If And Then VBA code

Re: Complicated? Multiple If And Then VBA code

Hello,

Whenever you are facing multiple Ifs ... you should move to Select Case ...

see video explanation ...https://www.youtube.com/watch?v=UBRECjc4QoM

Hope this will help

Re: Complicated? Multiple If And Then VBA code

VBA or Formula, two different options, both can be effective. If you want to use IF on just a regular sheet, then choose which order you want to test

Re: Complicated? Multiple If And Then VBA code

James006 said:

Hello,

Whenever you are facing multiple Ifs ... you should move to Select Case ...

see video explanation ...https://www.youtube.com/watch?v=UBRECjc4QoM

Hope this will help

Click to expand...

Not sure this is going to help with what I'm looking to do, since I have drop down selections for C4 and F6, which I need I4 to do the whole 'IF C4=X AND F4 = Y THEN I4 = Z" - The link you provided seems to want to have the values entered into a text box, when I have drop down selections for these already.

I tried to do an 'IF' formula, however I would only be able to fit 7 statements in before it throws an error - I need more than 7, as there's probably going to be around 30+ different options, hence why I opted for VBA.

Kind regards,

Re: Complicated? Multiple If And Then VBA code

Do you have a table/list of options and results?

Re: Complicated? Multiple If And Then VBA code

I'll give you an example list, in the following format;

IF C4 = Plat AND F4 = 2 THEN I4 = 4 (etc, etc)


List;

Plat | 2 | 4
Gold | 2 | 4
Silver | 2 | 4
Bronze | 2 | 8
Copper | 2 | 8

Plat | 3 | 8
Gold | 3 | 8
Silver | 3 | 8
Bronze | 3 | 8
Copper | 3 | 8

So on and so forth. It's going to be a rather large list, with various different outcomes, depending on the rest of the spreadsheet - as I said, it's a lot more than a normal IF statement could handle in a formula and I'm not proficient enough in VBA to figure out out myself.

Kind regards,

Re: Complicated? Multiple If And Then VBA code

theres an issue Bronze 2 & 3 = 8 plus copper

Re: Complicated? Multiple If And Then VBA code

If you had a table like the one you posted in A1:C11 you could use a formula like this to return the value from the 3rd column.

=INDEX(C1:C11,MATCH(E1&F1, A1:A11&B1:B11,0))

Note this is an array formula and needs to be entered with CTRL+SHIFT+ENTER.

Re: Complicated? Multiple If And Then VBA code

mole999 said:

theres an issue Bronze 2 & 3 = 8 plus copper

Click to expand...

mole999 said:

theres an issue Bronze 2 & 3 = 8 plus copper

Click to expand...

Not entirely sure what you're getting at? It's supposed to be like that.. this is what I'm saying, there's a LOT of data that this will need to pick from.

Norie said:

If you had a table like the one you posted in A1:C11 you could use a formula like this to return the value from the 3rd column.

=INDEX(C1:C11,MATCH(E1&F1, A1:A11&B1:B11,0))

Note this is an array formula and needs to be entered with CTRL+SHIFT+ENTER.

Click to expand...

Not sure if this would work, but let me see if I can explain it a better.. I'd rather not have to make any extra tables hidden in the spreadsheet as I already have for other things.

So, there's cell C4 which will contain several values (once they're all fleshed out) - but at present, it will simply contain 'Plat', 'Gold', 'Silver', 'Bronze' and 'Copper' in the form of a drop down list.

In cell F4 it will contain a numeric value between 1 and 4, also in the form of a drop down list.

In cell I4 it will output a value, e.g. "8", depending on what is selected in C4 and F4.

As mentioned above, for example, C4 contains 'Plat' as the option selected from the drop down. F4 contains '2' as the option selected from the drop down and I4 would show '4'
However, if C4 were to contain 'Copper' and F4 were to contain '4' then I4 would show '48' - the end value would be different, depending on the selection in the two drop-downs.

I hope this explains things a little more?

I would rather go with VBA, so that I don't accidentally break something with the formula post-completion.

Thank you all for your help so far.

Re: Complicated? Multiple If And Then VBA code

It really would be simplest to use a table, if you were to use VBA you would need multiple If statements and/or Select Case statements and you would need to hard-code all the values.

If at any time there was a need to change what a particular combination returned then you would need to go into the code and make the required changes.

I suppose in VBA you could use arrays but again the values would be hard-coded.