VBA code for multiple if statements within an Array

[Balajibenz]

Hi there,

Can you guys help me with below code. what I am trying to do is to split the array in column B which is seperated by commas and then look for each items and if the condition statisfies then it should publish something in column C. I have lot of scenarios with combination of both AND/OR conditions.
at the end if none of scenario is satisfied then in column "C" it should be "not defined".here is the code i have been working on.

Sub mapping()
   Dim Cl As Range
   Dim Dic As Object
   Dim Sp As Variant
   Dim i As Long
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("Analysis")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Sp = Split(Cl.Offset(, 1).Value, ",")
         Select Case Cl.Offset(, 1).Value
              Case Is = " "
              C1.Offset(, 2).Value = " "
              Case Is = "Production"
              C1.Offset(, 2).Value = "Prod"
              Case Is = "Production" And "Development" Or "Training"
              C1.Offset(, 2).Value = "Dev/Prod"
             
           End Select
Next Cl
End With
End Sub

below is the sample data.

ID	Category
121	Production
135	Production, Development, Staging, Test, Training, UserAcceptanceTest
283	Development, Test
1058	Production, Test
148	UserAcceptanceTest, Development
1125	Production, UserAcceptanceTest
1138

 

[Zot]

This probably not exactly what you want but it will give you the idea.

Sub mapping()
   
Dim x As Long
Dim rng As Range, Cl As Range
Dim Sp() As String, Combination As String
Dim KeywordSequence() As Variant, Element As Variant
Dim i As Long

KeywordSequence = Array("Production", "Development", "Staging", "Test", "Training", "UserAcceptanceTest")

With Sheets("Analysis")
    Set rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    For Each Cl In rng
        Sp = Split(Cl, ",", , 1)
        For x = 0 To UBound(Sp)                            ' To remove any leading/trailing space
            Sp(x) = Trim(Sp(x))
        Next
        For Each Element In Sp
            If IsInArray(CStr(Element), KeywordSequence) Then
                Select Case Element
                    Case "Production"
                        Key = "Prod"
                    Case "Development"
                        Key = "Dev"
                    Case "Staging"
                        Key = "Stag"
                    Case "Test"
                        Key = "Dev/Test"
                    Case "Training"
                        Key = "Train"
                    Case "UserAcceptanceTest"
                        Key = "Accept"
                End Select
            End If
            If Not Len(Key) = 0 Then Combination = Combination & Key & "/"
        Next
            Cl.Offset(, 2) = Left(Combination, Len(Combination) - 1)
            Combination = ""
    Next Cl
End With

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
' From Rick Rothstein
IsInArray = InStr(Chr(1) & Join(arr, Chr(1)) & Chr(1), Chr(1) & stringToBeFound & Chr(1)) > 0
End Function

 

[Balajibenz]

This probably not exactly what you want but it will give you the idea.

Sub mapping()
  
Dim x As Long
Dim rng As Range, Cl As Range
Dim Sp() As String, Combination As String
Dim KeywordSequence() As Variant, Element As Variant
Dim i As Long

KeywordSequence = Array("Production", "Development", "Staging", "Test", "Training", "UserAcceptanceTest")

With Sheets("Analysis")
    Set rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    For Each Cl In rng
        Sp = Split(Cl, ",", , 1)
        For x = 0 To UBound(Sp)                            ' To remove any leading/trailing space
            Sp(x) = Trim(Sp(x))
        Next
        For Each Element In Sp
            If IsInArray(CStr(Element), KeywordSequence) Then
                Select Case Element
                    Case "Production"
                        Key = "Prod"
                    Case "Development"
                        Key = "Dev"
                    Case "Staging"
                        Key = "Stag"
                    Case "Test"
                        Key = "Dev/Test"
                    Case "Training"
                        Key = "Train"
                    Case "UserAcceptanceTest"
                        Key = "Accept"
                End Select
            End If
            If Not Len(Key) = 0 Then Combination = Combination & Key & "/"
        Next
            Cl.Offset(, 2) = Left(Combination, Len(Combination) - 1)
            Combination = ""
    Next Cl
End With

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
' From Rick Rothstein
IsInArray = InStr(Chr(1) & Join(arr, Chr(1)) & Chr(1), Chr(1) & stringToBeFound & Chr(1)) > 0
End Function

That helped mate. Thank you