Excel VBA - using SELECT CASE but now need an array

Question

I've currently got the code below. There can only currently be seven values for "ins" and so the code suffices, however as of next month I have been told that there will be over 900 values!

I assume that rather than writing another 900 case statements I could use an array of some sort. Can anyone give me a nudge in the right direction?

Private Sub test()
Dim i As Long
Dim lr As Long
Dim ins As String

lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 6 To lr
Select Case Cells(i, 20)
    Case Is = "" 
        ins = Mid(Cells(i, 11), 14, 2)
            Select Case Cells(i, 10)
                Case "Inx", "ComInx"
                     Select Case Cells(i, 9)
                         Case "EINX"
                              Select Case ins
                                  Case "LD"
                                      Cells(i, 9).Value = "SR"
                                  Case "GP"
                                      Cells(i, 9).Value = "GAMA"
                                  Case "AV"
                                      Cells(i, 9).Value = "NU"
                                  Case "AX"
                                      Cells(i, 9).Value = "AXC"
                                  Case "MZ"
                                      Cells(i, 9).Value = "MZE"
                                  Case "AD"
                                      Cells(i, 9).Value = "AGD"
                                  Case "AG"
                                      Cells(i, 9).Value = "AG"
                              End Select
                     End Select
            End Select
End Select
Next

End Sub

there are arrays, collections, dictionaries, own types, etc.. research! — , Aug 19 '13 at 10:34
I suggest you use a lookup table stored in a range-hardcoding the values would be a maintenance nightmare — JosieP, Aug 19 '13 at 10:42

Ioannis · Accepted Answer · 2013-08-19T10:55:33.603

I would use a dictionary object for this. Here is a proof-of-concept based on your lines:

Private Sub test()
    Dim i As Long
    Dim lr As Long
    Dim ins As String        
    Dim rngCases As Range, rngCases2 As Range, rngCase As Range
    Dim dicCases As Dictionary

    lr = Range("A" & Rows.Count).End(xlUp).Row

    ' rngCases stores the possible values of ins
    ' Here I assume they are stored in col 40
    Set rngCases = Range(Cells(6, 40), Cells(lr, 40))

    ' rngCases2 stores the values you want to map for each value of ins.
    ' Here I assume they are stored in col 41
    ' No of entries = No of entries of rngCases
    Set rngCases2 = Range(Cells(6, 41), Cells(lr, 41))
    Set dicCases = New Dictionary

    For Each rngCase In rngCases
        If Not dicCases.Exists(rngCase.Value) Then
            dicCases.Add Key:=rngCase.Value, Item:=rngCases2.Value
        End If
    Next rngCase

    For i = 6 To lr
    Select Case Cells(i, 20)
        Case Is = ""
            ins = Mid(Cells(i, 11), 14, 2)
                Select Case Cells(i, 10)
                    Case "Inx", "ComInx"
                         Select Case Cells(i, 9)
                             Case "EINX"
                                ' We simply need to refer to the mapped value of ins
                                 If dicCases.Exists(ins) then
                                     Cells(i, 9) = dicCases.Item(ins)
                                 Else
                                     ' Throw an error or do something here
                                 End If
                         End Select
                End Select
    End Select
    Next

End Sub

To enable the Dictionary, go to Tools->References and select Microsoft Scripting Runtime.

I hope this gets you started!

Excel VBA - using SELECT CASE but now need an array

1 Answers1

Linked