VBA code for multiple if statements within an Array

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
80
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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.

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This probably not exactly what you want but it will give you the idea.

VBA Code:
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
 
Upvote 0
Solution
This probably not exactly what you want but it will give you the idea.

VBA Code:
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:)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top