Is an array inside a select case statement possible?

Question

I just want to know if it is possible for me to assign an array and use it as a qualifier inside a case statement?

Sub AccountCopy()
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim Acct As Variant
Dim NR As Integer

Criteria1 = Array("Checking", "Savings")
Criteria2 = Array("Loans", "Credit Card")

MonthSheet.Range("T1") = "Title"
MonthSheet.Range("U1") = "Account"
MonthSheet.Range("V1") = "Description"
MonthSheet.Range("W1") = "Amount"
MonthSheet.Range("X1") = "Date"
MonthSheet.Range("Y1") = "Category"

With Range("T1:Y1")
    .Font.Name = "Calibri"
    .Font.Size = 8
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .Style = "Title"
    .Columns.AutoFit
End With



For Each Acct In [AccountNameList]
    Select Case Acct.Offset(0, 1).Value
        Case Is = Criteria1
            NR = Range("T" & Rows.Count).End(xlUp).Row + 1 'Next Row
            'MonthSheet.Range 
        Case Criteria2
    End Select

Don't criticize me too hard, I'm still pretty new to this. I don't post on fourms very often, but their are some really talented people on here and I thought who better to ask then the people that have been coding for years? Thanks in advance!

This is what I want to accomplish: I want to define the "Criteria1" Array to how ever many dimensions I so desire. Perhaps I want to add a third criteria to the list. Rather than going and changing the case statement, I would rather just add to the array later on down the line to include that additional qualifier. Perhaps I've set the wrong type? I don't know? I feel like this can be done rather easy but I am missing a very small detail.

Answer 1

Some remarks:

1. You are defining a 1 dimension array and you don't need a two/three dimensional array to accomplish what you want. Check Mathieu's tutorial to learn about this
2. Always fully qualify the objects to avoid code unexpected behaviors
3. Indent your code (you can use the free www.rubberduckvba.com to help you with that)
4. What you want is totally doable...in different ways, one is just use a helper function to find the match in an array.

Read the code's comments and adjust it to fit your needs

Helper function option

Option Explicit

Public Sub AccountCopy()

    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim Acct As Variant ' This can be declared as a Range
    Dim NR As Integer ' Better to name the variables to something readable like: newRow

    ' Added this new variable
    Dim accountValue As String

    Criteria1 = Array("Checking", "Savings")
    Criteria2 = Array("Loans", "Credit Card")

    MonthSheet.Range("T1") = "Title"
    MonthSheet.Range("U1") = "Account"
    MonthSheet.Range("V1") = "Description"
    MonthSheet.Range("W1") = "Amount"
    MonthSheet.Range("X1") = "Date"
    MonthSheet.Range("Y1") = "Category"

    ' This next line should be something like: <TheCodeNameOfTheSheet>.Range("T1:Y1") to be fully qualified
    With Range("T1:Y1")
        .Font.Name = "Calibri"
        .Font.Size = 8
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Style = "Title"
        .Columns.AutoFit
    End With


    ' In here, it's easier to understand if you use Thisworkbook.Names("AccountNameList")
    For Each Acct In [AccountNameList]

        ' Store the account next cell's value
        accountValue = Acct.Offset(0, 1).Value

        Select Case True
        Case IsInArray(accountValue, Criteria1)
            ' Do something
            Debug.Print "Something", Acct.Offset(0, 1).Address
        Case IsInArray(accountValue, Criteria2)
            ' Do something else
            Debug.Print "Something else", Acct.Offset(0, 1).Address
        End Select

    Next Acct

End Sub

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    ' Credits: https://stackoverflow.com/a/11112305/1521579
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Another approach is to loop through the array items

with something like this...

Add this inside the for each loop

needs a dim counter as long somewhere in the code

For counter = 0 To UBound(Criteria1)
    If Acct.Offset(0, 1).Value = Criteria1(counter) Then
        ' Do something
        Debug.Print "Something", Acct.Offset(0, 1).Address
    End If
Next counter

For counter = 0 To UBound(Criteria2)
    If Acct.Offset(0, 1).Value = Criteria2(counter) Then
        ' Do something else
        Debug.Print "Something else", Acct.Offset(0, 1).Address
    End If
Next counter