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! — user2140173
– user2140173, Commented Aug 19, 2013 at 10:34
I suggest you use a lookup table stored in a range-hardcoding the values would be a maintenance nightmare — JosieP
– JosieP, Commented Aug 19, 2013 at 10:42

Ioannis · Accepted Answer · 2013-08-19 10:55:33Z

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!

Collectives™ on Stack Overflow

Excel VBA - using SELECT CASE but now need an array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related