VBA Data Structure Issues

[Nandeo Tifur]

I am having difficulty in figuring out what kind of data structure I need to utilize. I am wanting the user to type in the code to get the nested values to be passed as variables (Ex “Country1/Product2/Code1”).

• Product 1
  • Country1
    • Code1
  • Country2
    • Code2
  • Country3
    • Code3
• Product 2
  • Country1
    • Code1
  • Country2
    • Code2
  • Country3
    • Code3
  • Country4
    • Code4
  • Country5
    • Code5(duplicate code of product 3)
  • Country6
    • Code6
• Product 3
  • Country1
    • Code1
  • Country2
    • Code2
  • Country3
    • Code3
  • Country4
    • Code4
  • Country5
    • Code5 (duplicate code of product 2)
  • Country6
    • Code6

I started creating a Dictionary and realized that one of the Codes is present in two different products. These codes represent specific items and changing its value would cause issues with the end user. I thought of using arrays, but don’t know how I would be able to functionally code over 36 different codes across 3 products. I initially thought that I could have a string in an array:

Prod = Array(“UK/Prod1/Code1”, “MX/Prod1/Code2”, “AG/Prod2/Code3”, …..)

The issue I had with that was trying to find the code within the array without specifying the whole string. Any ideas?

 

[Nandeo Tifur]

I have since built this with a Do While Loop with an If-ElseIf statement. I have over 36 codes to check against so it seems extensive on top of the other functions I'm implementing elsewhere. Is there a more concise way to code this?

Do    iProductCo = UCase(inputbox("Please input the Code."))
    
    If iProductCo = vbNullString Then
        Exit Sub
    End If
    
    If iProductCo = "CO1" Then
        iProduct = "Country1/Product1/CO1/"
        result = True
    ElseIf iProductCo = "CO2" Then
        iProduct = "Country2/Product1/CO2"
        result = True
    ElseIf iProductCo = "CO3" Then
        iProduct = "Country3/Product1/CO3"
        result = True
    ElseIf iProductCo = "CO4" Then
        iProduct = "Country4/Product2/CO4"
        result = True
    ElseIf iProductCo = "CO5" Then
        iProduct = "Country5/Product2/CO5"
        result = True
    ElseIf iProductCo = "CO6" Then
        iProducts = inputbox("Please Input if Product2 or Product3")
            If iProducts = vbNullString Then
                Exit Sub
            End If
            If iProducts = "Product2" Then
                iProduct = "Country6/Product2/CO6"
                result = True
            ElseIf iProducts = "Product3" Then
                iProduct = "Country7/Product3/CO6"
                result = True
            End If
    ElseIf iProductCo = "CO7" Then
        iProduct = "Country8/Product3/CO8"
        result = True
    Else
        MsgBox ("Please input a valid CO Code")
                result = False
    End If
   
    Loop While result = False
    
    MsgBox (iProduct)

Thanks in advance.