Excel VBA-使用SELECT CASE，但现在需要一个数组 [英] Excel VBA - using SELECT CASE but now need an array

问题描述

我目前有下面的代码.目前，"ins"只能有七个值，因此代码就足够了，但是，自下个月起，我被​​告知将有900个以上的值！

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!

我假设不用写另外的900个case语句，而是可以使用某种数组.谁能按正确的方向给我推一下?

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

推荐答案

为此，我将使用字典对象.这是根据您的观点而提出的概念证明:

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

要启用Dictionary，请转到Tools->References并选择Microsoft Scripting Runtime.

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

希望这可以帮助您入门！

I hope this gets you started!

这篇关于Excel VBA-使用SELECT CASE，但现在需要一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！