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,

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

This,

 Case SA = Platinum

should be this.

 Case  "Platinum"

Also, this,

Range("I4").Value = SL

should come after the end of the main Select Case.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

This,

 Case SA = Platinum

should be this.

 Case  "Platinum"

Also, this,

Range("I4").Value = SL

should come after the end of the main Select Case.

So, I now have the following;

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


Select Case SALV


 Case "Platinum"
       Select Case SV
           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

So, I've changed the Case SA = Platinum to Case "Platinum" as advised, however it still doesn't take into consideration cell C4 when running the Case.. also, moving Range("I4").Value = SL after the end of the main Select Case, stops it from working altogether. :S

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

How exactly are you running the code?

Where did you move this line to?

 Range("I4").Value = SL

Have you tried stepping through the code with F8?

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

How exactly are you running the code?

Where did you move this line to?

 Range("I4").Value = SL

Have you tried stepping through the code with F8?

Running the code through a button that I've macro'ed this to.

Select Case SALV




 Case "Platinum"
       Select Case SV
           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"
        Range("I4").Value = SL
        End Select




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

Not sure if the Bold will show, however I've had the code in the 3 places shown above. I've not tried stepping through with F8 - what would this do? I can give it a try and see what happens.

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

Stepping through the code with F8 will show you what's actually happening in the code.

By the way, what's SALV?

I don't see it declared or given a value anuywhere in the posted code.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

Stepping through the code with F8 will show you what's actually happening in the code.

By the way, what's SALV?

I don't see it declared or given a value anuywhere in the posted code.

What should it do when I hit F8? It just seems to highlight certain parts in yellow, but that doesn't really mean anything to me.

SALV was just a placeholder, really. I wasn't entirely sure what should go after the 'Select Case' statement. Looking at an example of how to do Select Case had 'Select Case test_expression' - What should I put there instead? I hadn't actually thought about that part.. maybe that's where I'm going wrong.

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

If C4 is where the user will select 'Platinum' or whatever then the first Select Case should look like this.

Select Case SA

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

If C4 is where the user will select 'Platinum' or whatever then the first Select Case should look like this.

Select Case SA

So, if I amend the code it should look like the following?;

Select Case SA


 Case "Platinum"
       Select Case SV
           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

Would this be correct?

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

More like this.

Dim SA As String
Dim SL As String
Dim SV As Long

    SA = Range("C4").Value
    SV = Range("F4").Value

    Select Case SA
        Case "Platinum"
            Select Case SV
                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
    End Select
    
    Range("I4").Value = SL

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

More like this.

Dim SA As String
Dim SL As String
Dim SV As Long

    SA = Range("C4").Value
    SV = Range("F4").Value

    Select Case SA
        Case "Platinum"
            Select Case SV
                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
    End Select
    
    Range("I4").Value = SL

I tried the above, exactly how it's written (literally copy-pasted, saved my current code just in case I need to go back to it) and it's not outputting to I4 when I click the button, no matter what is in C4 or F4.