VBA code for multiple if statements within an Array

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,012
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Balajibenz

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

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,947
Members
416,144
Latest member
JohnMacDonald

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
Top