Type 13 mismatch in VBA with Case statements

1
votes

I'm relatively new to programming in excel/vba, but I've been tasked with making a simple button in excel. The purpose of this is to use user inputs in one range and then return a value in another range after checking certain conditions. Below you can find my code. So I first associated variables with specific cells. Then in the button_click bit it is to check against the cases/nested if...then statements. The issue is it returns a Type 13 mismatch at the "Case 'H232' Or 'H250'" line and I'm not sure how to fix it. Thanks in advance!

EDIT: To give more detail on the project. Users are to enter the Hazard Codes (H-Codes) used by the GLobal Harmonized System (GHS) for hazard labeling of different products. Users are to enter the appropriate codes in the range H_Code_Input (which I now understand is incorrect). The code is then intended to check user inputs against the conditions outlined in my Select Case, and then produce data in the cells assigned. The numbered output signals either flagged conditions or levels of severity. I will try to set it up as a For Loop running through the column in place of using the H_Code_Input string.

Also I apologize for taking a few days to edit the question. I was out of town this past weekend.

Private Sub GetCellValue()
    'Preparing variable values for GHS by assigning them to output cells
    Dim Pyrophoric As Range
        Set Pyrophoric = Range("G5")
    Dim Gas_Under_Pressure As Range
        Set Gas_Under_Pressure = Range("G6")
    Dim Flammable_Gas As Range
        Set Flammable_Gas = Range("G7")
    Dim Liquefied_Gas As Range
        Set Liquefied_Gas = Range("G8")
    Dim Flammable_Liquid As Range
        Set Flammable_Liquid = Range("G9")
    Dim Oxidizing_Gas As Range
        Set Oxidizing_Gas = Range("G10")
    Dim Skin_Corrosion As Range
        Set Skin_Corrosion = Range("G11")
    Dim Eye_Irritant As Range
        Set Eye_Irritant = Range("G12")
    Dim Respiratory_Sensitizer As Range
        Set Respiratory_Sensitizer = Range("G13")
    Dim Skin_Sensitizer As Range
        Set Skin_Sensitizer = Range("G14")
    Dim Germ_Cell_Mutagen As Range
        Set Germ_Cell_Mutagen = Range("G15")
    Dim Carcinogen As Range
        Set Carcinogen = Range("G16")
    Dim Respiratory_Hazard As Range
        Set Respiratory_Hazard = Range("G17")
    Dim STORE As Range
        Set STORE = Range("G18")
    Dim STOSE As Range
        Set STOSE = Range("G19")
    Dim Environ_Acute As Range
        Set Environ_Acute = Range("G20")
    Dim Environ_Chronic As Range
        Set Environ_Chronic = Range("G21")
    Dim Ozone_Deplete As Range
        Set Ozone_Deplete = Range("G22")


    'Creating input range
    Dim H_Code_Input As Range
        Set H_Code_Input = Range("B5:B24")




End Sub

Private Sub CommandButton2_Click()
    'Establishing outputs based on user input H codes
    Select Case H_Code_Input
        Case H_Code_Input = "H232" Or "H250"
            Pyrophoric = 1
        Case "H280"
            Gas_Under_Pressure = 1
        Case "H224" Or "H225" Or "H226" Or "H227"
            If H_Code_Input.Value = "H224" Then
                Flammable_Gas = 1
            ElseIf H_Code_Input.Value = "H225" Then
                Flammable_Gas = 2
            ElseIf H_Code_Input.Value = "H226" Then
                Flammable_Gas = 3
            ElseIf H_Code_Input.Value = "H227" Then
                Flammable_Gas = 4
            End If
        Case "H270"
            Oxidizing_Gas = 1
        Case "H314" Or "H315" Or "H316"
            If H_Code_Input.Value = "H314" Then
                Skin_Corrosion = "1A, B, C"
            ElseIf H_Code_Input.Value = "H315" Then
                Skin_Corrosion = 2
            ElseIf H_Code_Input.Value = "H314" Then
                Skin_Corrosion = 3
            End If
        Case "H318" Or "H319" Or "H320"
            If H_Code_Input.Value = "H318" Then
                Eye_Irritant = 1
            ElseIf H_Code_Input.Value = "H319" Then
                Eye_Irritant = "2A"
            ElseIf H_Code_Input.Value = "H320" Then
                Eye_Irritant = "2B"
            End If
        Case "H334"
            Respiratory_Sensitizer = "1, 1A, 1B"
        Case "H317"
            Skin_Sensitizer = 1
        Case "H340" Or "H341"
            If H_Code_Input.Value = "H340" Then
                Germ_Cell_Mutagen = "1A, 1B"
            ElseIf H_Code_Input.Value = "H341" Then
                Germ_Cell_Mutagen = 2
            End If
        Case "H350" Or "H351-i" Or "H351"
            If H_Code_Input.Value = "H350" Or "H350-i" Then
                Carcinogen = "1A, 1B"
            ElseIf H_Code_Input.Value = "H351" Then
                Carcinogen = 2
            End If
        'Need Respiratory Hazard parameters
        Case "H372" Or "H373"
            If H_Code_Input.Value = "H372" Then
                STORE = 1
            ElseIf H_Code_Input.Value = "H373" Then
                STORE = 2
            End If
        Case "H335" Or "H370" Or "H371"
            If H_Code_Input.Value = "H335" Then
                STOSE = 3
            ElseIf H_Code_Input.Value = "H370" Then
                STOSE = 1
            ElseIf H_Code_Input.Value = "H371" Then
                STOSE = 2
            End If
        Case "H400" Or "H401" Or "H402"
            If H_Code_Input.Value = "H400" Then
                Environ_Acute = 1
            ElseIf H_Code_Input.Value = "H401" Then
                Environ_Acute = 2
            ElseIf H_Code_Input.Value = "H402" Then
                Environ_Acute = 3
            End If
        Case "H410" Or "H411" Or "H412" Or "H413"
            If H_Code_Input.Value = "H410" Then
                Environ_Chronic = 1
            ElseIf H_Code_Input.Value = "H411" Then
                Environ_Chronic = 2
            ElseIf H_Code_Input.Value = "H412" Then
                Environ_Chronic = 3
            ElseIf H_Code_Input.Value = "H413" Then
                Environ_Chronic = 4
            End If
        Case "H420"
            Ozone_Deplete = 1

    End Select

End Sub
3
excelvba
Typo - should be Case "H232" Or "H250" but I don't think your code can work because H_Code_Input is a multi-cell range. – SJR
You might want to consider an array instead of your list of ranges, and possibly a look up table to avoid all those Cases. – SJR
Add Option Explicit to the top of the module. And read this for more detail on scope. – BigBen
I can't shake the feeling this is totally overengineered. It would be really useful to see sample data, expected outcome and an explanation of what you are trying to accomplish. – cybernetic.nomad
You can't compare a multi-cell range to a string. You have to loop through the range and compare each individual cell. – BigBen

3 Answers

4
votes
Case "H224" Or "H225" Or "H226" Or "H227"

That parses as a bitwise-or expression, which is illegal with String operands; you want a comma-separated list of alternatives:

Case "H224", "H225", "H226", "H227"

Note that this only addresses the type mismatch error you're getting - there are several other important problems with this code.

I would warmly recommend reading about scoping, and checking the "require variable declaration" box in the VBIDE options, so that Option Explicit is systematically added to every module.

Rubberduck (disclaimer: I manage this open-source project) code inspections would warn you about all these unused variables, and all the implicitly-declared ones too.

0
votes

Something like this:

'...
Case "H318" Or "H319" Or "H320"
        If H_Code_Input.Value = "H318" Then
            Eye_Irritant = 1
        ElseIf H_Code_Input.Value = "H319" Then
            Eye_Irritant = "2A"
        ElseIf H_Code_Input.Value = "H320" Then
            Eye_Irritant = "2B"
        End If
 '...

is functionallly equivalent (assuming you were to incorporate Mathieu's fix) to:

        '...
        Case "H318": Eye_Irritant = 1
        Case "H319": Eye_Irritant = "2A"
        Case "H320": Eye_Irritant = "2B"
        '...
0
votes

This new code returns the proper values as intended. I am going to now work on printing out more information regarding the H-Code in other columns. Thanks for the help guys!

Option Explicit

Private Sub CommandButton1_Click() 'Set counter variable Dim i As Integer

'Preparing variable values for GHS by assigning them to output cells
Dim Pyrophoric As Range
    Set Pyrophoric = Range("G5")
Dim Gas_Under_Pressure As Range
    Set Gas_Under_Pressure = Range("G6")
Dim Flammable_Gas As Range
    Set Flammable_Gas = Range("G7")
Dim Liquefied_Gas As Range
    Set Liquefied_Gas = Range("G8")
Dim Flammable_Liquid As Range
    Set Flammable_Liquid = Range("G9")
Dim Oxidizing_Gas As Range
    Set Oxidizing_Gas = Range("G10")
Dim Skin_Corrosion As Range
    Set Skin_Corrosion = Range("G11")
Dim Eye_Irritant As Range
    Set Eye_Irritant = Range("G12")
Dim Respiratory_Sensitizer As Range
    Set Respiratory_Sensitizer = Range("G13")
Dim Skin_Sensitizer As Range
    Set Skin_Sensitizer = Range("G14")
Dim Germ_Cell_Mutagen As Range
    Set Germ_Cell_Mutagen = Range("G15")
Dim Carcinogen As Range
    Set Carcinogen = Range("G16")
Dim Respiratory_Hazard As Range
    Set Respiratory_Hazard = Range("G17")
Dim STORE As Range
    Set STORE = Range("G18")
Dim STOSE As Range
    Set STOSE = Range("G19")
Dim Environ_Acute As Range
    Set Environ_Acute = Range("G20")
Dim Environ_Chronic As Range
    Set Environ_Chronic = Range("G21")
Dim Ozone_Deplete As Range
    Set Ozone_Deplete = Range("G22")

'For Loop to run Select Case to print category
For i = 5 To 24
    Select Case Cells(i, 2)
        Case "H232", "H250":    Pyrophoric = 1
        Case "H280":            Gas_Under_Pressure = 1
        Case "H220":            Flammable_Gas = 1
        Case "H221":            Flammable_Gas = 2
        Case "H224":            Flammable_Liquid = 1
        Case "H225":            Flammable_Liquid = 2
        Case "H226":            Flammable_Liquid = 3
        Case "H227":            Flammable_Liquid = 4
        Case "H270":            Oxidizing_Gas = 1
        Case "H314":            Skin_Corrosion = "1A, 1B, 1C"
        Case "H315":            Skin_Corrosion = 2
        Case "H316":            Skin_Corrosion = 3
        Case "H318":            Eye_Irritant = 1
        Case "H319":            Eye_Irritant = "2A"
        Case "H320":            Eye_Irritant = "2B"
        Case "H334":            Respiratory_Sensitizer = "1, 1A, 1B"
        Case "H317":            Skin_Sensitizer = 1
        Case "H340":            Germ_Cell_Mutagen = "1A, 1B"
        Case "H341":            Germ_Cell_Mutagen = 2
        Case "H350", "H350-i":  Carcinogen = "1A, 1B"
        Case "H351":            Carcinogen = 2
        Case "H372":            STORE = 1
        Case "H373":            STORE = 2
        Case "H335":            STOSE = 3
        Case "H370":            STOSE = 1
        Case "H371":            STOSE = 2
        Case "H400":            Environ_Acute = 1
        Case "H401":            Environ_Acute = 2
        Case "H402":            Environ_Acute = 3
        Case "H410":            Environ_Chronic = 1
        Case "H411":            Environ_Chronic = 2
        Case "H412":            Environ_Chronic = 3
        Case "H413":            Environ_Chronic = 4
        Case "H420":            Ozone_Deplete = 1
    End Select

Next i

End Sub