VBA code for multiple if statements within an Array

Question

I am trying to split the array in column B which is separated by commas and then look for each item and if the condition is satisfied then publish something in column C.

I have lot of scenarios with combination of both AND/OR conditions.
At the end if none of scenarios is satisfied then in column "C" it should be "not defined".

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

Sample data where column A has Id and column B has category.

ID	CATEGORY
131	Production
124	Production, Development, Staging, Test, Training, UserAcceptanceTest
283	Development, Test
1138	empty.

I am looking for below result in column "C" for the below mentioned scenarios.
If Category column is as below then Column "C" values should be the one after "-".

empty - No
Development - Dev
Production - Prod
Test - Test
Staging - Staging
Training - Training
UserAcceptanceTest - UAT
Development AND Test AND Production OR any other category - All
Development AND Test OR any other category (Except Production) - Dev/Test
Test AND Production OR any other category (Except Development) - Dev/Test
Development AND any other category (Except Production and Test ) - Dev
Production AND any other category (Except Development and Test ) - Prod
Test AND any other category (Except Development and Production) - Test
any other scenario - Not Defined

Would really help your question to show some sample input data and the expected output. — Tim Williams, Dec 14 '20 at 06:36
You can edit your question, and format the data as it would appear on the sheet. — Tim Williams, Dec 14 '20 at 06:46
Have a look at [this Answer](https://stackoverflow.com/a/11112305/9538700) — horst, Dec 14 '20 at 06:48
Hi horst, that seems to be to search for a single string in an array but I have to look for multiple strings with AND and OR conditions which I am finding difficult to do. — Wafee77, Dec 14 '20 at 06:58
yes, just use the `IsInArray`Function multiple times in an `If` or `case` Statement. Like `If IsInArray("Foo", arr) And IsInArray("Bar", arr) Then...` — horst, Dec 14 '20 at 07:00
Thank you Horst, do we need to split the array if we are using IsInArray? Can you help me on how to iterate and through each array in column B and produce result in column C. I can re-use write all my conditions. And how to validate a case where only the string we are searching is in the array and nothing else. — Wafee77, Dec 14 '20 at 07:12
Do 2-7 mean only one entry? Could you clarify (do a rethink) on 8-13 i.e. 8. Development, Test, Anything, 9. Development, Anything (except) 10. Test, Anything (except) which would mean something different if you would replace the ORs with ANDs. Is the order set in 8-13? Think about `equal` and `contains`. — VBasic2008, Dec 14 '20 at 08:12
I don't think that this will work without sample data that will clearly show all the cases. — VBasic2008, Dec 14 '20 at 08:22

horst · Accepted Answer · 2020-12-14T08:19:49.083

Using the function IsInArray from This Answer:

I filled it with some example cases to give you the idea how to do it.

Sub Category()

For Each cl In Range("A2:A" & Range("A2").End(xlDown).Row)

    Sp = Split(tempString , ", ")

    Select Case True    
        Case UBound(Sp) = -1
            cl.Offset(0, 2).Value = "No"
        Case UBound(Sp) = 0 And Sp(0) = " "
            cl.Offset(0, 2).Value = "No"
        Case UBound(Sp) = 0 And Sp(0) = "Development"
            cl.Offset(0, 2).Value = "Dev"
        Case IsInArray("Development", Sp) And IsInArray("Test", Sp) And IsInArray("Production", Sp)
            cl.Offset(0, 2).Value = "All"
        Case IsInArray("Development", Sp) And Not IsInArray("Production", Sp) And Not IsInArray("Test", Sp)
            cl.Offset(0, 2).Value = "Dev"
        Case Else
            cl.Offset(0, 2).Value = "Not Defined"
    End Select

Next cl
End Sub


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Hi horst, that works like a gem except for the line - Case UBound(Sp) = 0 And Sp(0) = " " it is throwing Run-tume error '9': Subscript out of range . Until it finds the first empty cell in category it is running great. — Wafee77, Dec 14 '20 at 08:15

VBA code for multiple if statements within an Array

1 Answers1