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

Sorry can't really help further without seeing your worbook.

Is there any chance you could remove sensitive date and then upload it somewhere?

I've removed everything else in the work book (other sheets, data, etc.) and just left the C4, F4 and I4 fields completed with the information as per above posts.

Your code is in there, too.

Here's the link; https://www.dropbox.com/s/tutlpa2t334a7ug/Worksheet.xlsm?dl=0

 

[Norie]

Re: Complicated? Multiple If And Then VBA code

The problem was 'PLATINUM' - the code is looking for 'Platinum'.

Try this.

Option Explicit

Sub Test()
Dim SA As String
Dim SL As String
Dim SV As Long

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

    Select Case UCase(SA)
        Case UCase("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
        Case Else
            SL = "Nothing selected"
    End Select
    
    Sheets("Sheet6").Range("I4").Value = SL
    
End Sub

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

The problem was 'PLATINUM' - the code is looking for 'Platinum'.

Try this.

Option Explicit

Sub Test()
Dim SA As String
Dim SL As String
Dim SV As Long

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

    Select Case UCase(SA)
        Case UCase("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
        Case Else
            SL = "Nothing selected"
    End Select
    
    Sheets("Sheet6").Range("I4").Value = SL
    
End Sub

Awesome, it works! I should have really noticed that it was capitalized.. would have saved us both a lot of trouble, haha. Thanks! I should be able to finish off the rest of the sheet now - but I know where to go if I need a hand.

 

[Daeksoul]

Re: Complicated? Multiple If And Then VBA code

Quick question, can I just copy-paste the above for the different values that will fill C4? Or would I need to add in anything extra to get it to follow the Select Case sequence?