Multiple If And Then VBA code

[Daeksoul]

Hi guys,
New to the forum, and also pretty much a newbie when it comes to Formula/VBA for Excel.

I'm trying to get some VBA code working (as I know I would be unable to do what I need with a formula, due to the size of it) but I'm not sure where to start.

Basically, I'll lay it out logically;

IF C4 = TextValue AND F4 = NumericValue THEN I4 = Value

However, it would be a case of having multiple of the above, for different values for C4 and F4, all outputting to I4, for example;

IF C4 = "Gold" AND F4 = "2" THEN I4 = "12 Hours"
IF C4 = "Silver" AND F4 = "3" THEN I4 = "8 Hours"

If you can see where I'm going with this? It's a little difficult to explain what I need, other than writing it like this..

I'll also need to know how to assign the above VBA code to the cell I4 so that it outputs the right value, unless that would be automatic with it saying I4 = Value?

Any help would be greatly appreciated.

Kind regards,

 

[James006]

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

 

[mole999]

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

 

[Daeksoul]

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

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,

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

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

 

[Daeksoul]

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,

 

[mole999]

Re: Complicated? Multiple If And Then VBA code

theres an issue Bronze 2 & 3 = 8 plus copper

 

[Norie]

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.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

theres an issue Bronze 2 & 3 = 8 plus copper

theres an issue Bronze 2 & 3 = 8 plus copper

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.

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.

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.

 

[Norie]

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.