CODEWITHSUNDEEP
vbaif-statement
Sam
Sam

Reputation: 55

Multiple ElseIf queries in VBA

In my previous post I have received an enormous support. My problem has been solved, the code has been established and works perfect. Here is the link to the code: https://stackoverflow.com/a/64042549/14332329

Now I have some countries in the table (i.e. „Bulgaria“) which obey to the same rules as Malaysia & Indonesia (for which the code has been written), but additionally they should also be marked as „Bulgaria Food CL“ when the entry in the column B = „ISO 22000“ (in this case the entry in column C is „Food“). Can it also be integrated in this code? This is how the table looks right now. Column G should show the VBA result.

enter image description here

a) There are countries like Croatia, Czech Republic, Poland, Turkey, Indonesia, Malaysia, which are CL (= certifying location) for some standards like ISO9001, ISO14001, OHSAS18001, ISO50001. These ISO-Standards belong to the group „IMS“ in the column C. In this case I would like to have them in the column G like „Bulgaria IMS CL“

b) There are some countries like Greece, which are CL for the same standards as above except for ISO50001. In this case I would like to have them in the column G like „Greece IMS CL“

c) There are also some countries like Bulgaria, which obey to the same rules as under a) plus additionally these countries are CL for other standards like ISO2200 (group in column C = „Food“) or FSC (group in column C = „Sustainability“). In this case I would like to have them in the column G like „Bulgaria Food CL“

d) For all other cases I would like to have the result in column G like „Bulgaria IMS NC“ or „Bulgaria Food NC“ or whatsoever according to the rule „Country from column D + Group from column C + NC“

I have tried to leave the same comment in the original post, however it seems, that no one looks there for many days already. I hope this post can be left and not deleted. Otherwise, I would be happy about comments, how to make it right. Thank you so much in advance!

Upvotes: 0

Views: 76

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Try,

Public Sub CL_NC()

    'Looks like your whole range is between A:G columns, so we insert that data
    'inside the array
    Dim sCLNC As String
    Dim rngDB As Range
    Dim i  As Long
    Dim arr As Variant
    Dim Country As String
    
    With ThisWorkbook.Worksheets("Gesamt")
        'Last row
         i = .Cells(.Rows.Count, 1).End(xlUp).Row
        'insert your data into the array
        Set rngDB = .Range("A1:G" & i)
        arr = rngDB
        'Loop through row 2 to the last (inside the array
        For i = 2 To UBound(arr)
            Country = Trim(Replace(arr(i, 4), "Cert ", ""))
            sCLNC = getCLorNC(Country, arr(i, 2))
            arr(i, 7) = Country & " " & arr(i, 3) & " " & sCLNC
        Next i
        rngDB = arr
    End With

End Sub
Function getCLorNC(Nation As String, Regelwerk As Variant)
    Dim a As Variant, b As Variant
    
    Select Case Nation
    Case "Croatia", "Czech Republic", "Poland", "Turkey", "Indonesia", "Malaysia"
        a = Array("ISO 9001", "ISO 14001", "OHSAS 18001", "ISO 50001")
    Case "Greece"
        a = Array("ISO 9001", "ISO 14001", "OHSAS 18001")
    Case "Bulgaria"
        a = Array("ISO 9001", "ISO 14001", "OHSAS 18001", "ISO 50001", "ISO 22000")
    Case Else
        getCLorNC = "NC"
        Exit Function
    End Select
    For Each b In a
        If InStr(Regelwerk, b) Then
            getCLorNC = "CL"
            Exit Function
        Else
            getCLorNC = "NC"
        End If
    Next b
End Function

Upvotes: 1

Related Questions