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,

 

[Daeksoul]

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.

You'll have to speak in layman's terms, I'm not entirely sure how most Excel formula/etc. works or how it needs to be written. Could you give me an example, by any chance?

Thanks.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

You'll have to speak in layman's terms, I'm not entirely sure how most Excel formula/etc. works or how it needs to be written. Could you give me an example, by any chance?

Thanks.

Also, just something to help you visualize how it looks on the spreadsheet (providing it shows up - if it doesn't, right click the 'image' icon and copy the image URL / open in a new tab if you are able);

As you can see, C4 will contain a string, F4 will contain a numeric value and I4 will output a string depending on what C4 & F4 say.

The values of C4 and F4 will never change are both drop-down lists, once I've set them all up - I4 will be automatically filled depending on what the other cells have in them - but I want to be sure that I can get the code to work before I go about adding all the options

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

I posted an example formula in post #8 based on the example data you posted in post #6 being in the range A11:B11 and the user selecting from dropdowns in E1 and F1.

If those ranges/cells don't correspond to what you have then it shouldn't be too difficult to adjust the formula appropriately.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

I posted an example formula in post #8 based on the example data you posted in post #6 being in the range A11:B11 and the user selecting from dropdowns in E1 and F1.

If those ranges/cells don't correspond to what you have then it shouldn't be too difficult to adjust the formula appropriately.

Ah, right - I didn't understand the formula so I left it alone - I've actually started to try using the Select Case that had been mentioned - researching further into it, it MIGHT work.. however I'm running into a problem;

Dim SA As String
Dim SV As String
Dim SL As String
SA = Range("C4").Value
SV = Range("F4").Value




Select Case SA


   Case SA = Platinum And SV = 4
      SL = "12 Hours"


  Case SA = Platinum And SV = 3
    SL = "8 Hours"


   '...




Range("I4").Value = SL
End Select

The first Case works fine when I click the button, when the drop-downs are filled. However, when the second Case selections (Platinum & 3) are selected, it doesn't work. Am I missing something between cases for it to work correctly? :S

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

You can't use Select Case to check for multiple values like that.

Something like this,

 SA = "Platinum" And SV = 4

will return a boolean value True/False.

What you could do is have nested Select Cases.

Select Case SA

   Case SA = "Platinum"
       Select Case SV
           Case 4
               SL = "12 Hours"
           Case 3
               SL = "8 Hours"
           ' other SV cases
           '
           '
        End Select

    ' other SA cases    
    '
    '
End Select

Something like that should work but all the values would need to be hard-coded.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

Something like that should work but all the values would need to be hard-coded.

Not sure what you mean by 'hard-coded' - Again, not the best with VBA, but I'm trying to learn, probably throwing myself in at the deep end but eh. :P

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

Hard-coded means you have to enter all the data into the code rather than reading them from a sheet/file.

When you hard-code data in code it means you have to change the code if there are any changes.

hard-code

fix (data or parameters) in a program in such a way that they cannot be altered without modifying the program.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

Hard-coded means you have to enter all the data into the code rather than reading them from a sheet/file.

When you hard-code data in code it means you have to change the code if there are any changes.

That wouldn't be a problem. The values aren't going to change from time to time - Once they're all in, they're staying as they are.

The first part of the code I did;

   Case SA = Platinum And SV = 4      SL = "12 Hours"

Works fine (on a button, at least) it outputs the "12 hours" to I4 as intended, however if I add anything after that, it just stops working altogether.

I tried the above code you provided, but it doesn't seem to be doing anything at all. I've tried applying it to a button also with no change. (I'd rather not have to use buttons if possible, to make it seamless and as automated as possible)

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

You cannot use this in a Select Case statement.

Select Case SA
   Case SA = Platinum And SV = 4

First of all since it has no quotes around it Platinum is seen as an undeclared variable, if you want it to be regarded as a string enclose it in quotes.

Even with quotes the expression SA = "Platinum" And SV = 4 will return a boolean value True/False which you can't really compare to a string value, ie the value in SA.

As for not using a button, you've got 2 options really.

1 Use a formula like the one I posted earlier.

2 Use worksheet event code, eg a change event.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

Ok, so.. So far I've got this;

Dim SA As String
Dim SV As String
Dim SL As String
SA = Range("C4").Value
SV = Range("F4").Value


Select Case SALV


 Case SA = Platinum
       Select Case Sev
           Case 4
               SL = "12 Hours"
           Case 3
               SL = "8 Hours"
           Case 2
               SL = "4 Hours"
           Case 1
                SL = "2 Hours"
           Case 0
                SL = "1 Hour"
                
        End Select


    Range("I4").Value = SL

End Select

It works, but doesn't seem to be factoring in C4 being Platinum, as when I change it to Gold or any other value, it still runs the Case based on F4 and outputs to I4.. so it's partly working. Getting there slowly but surely!

I've tried Case SA = Platinum both with quotation and without quotation, even though it's declared and mentioned in the case, it seems to be ignoring it altogether. Most likely I'm entering something wrong somewhere, haha. :D