Select Case and Arrays?

[hypnotoad]

I'm trying to make a select case statement that would execute certain code depending on which array a user entered text string is stored in. I'm not using the code tag because I'm abbreviating (I hope that alright)...

Case Array1
do this
Case Array2
do this
etc...

I've tried a few ways of of writing the case statement, but Excel is not liking any of them. Is what I'm trying to do futile?

 

[diddi]

what do you mean by 'array'? have you any code you have tried?

 

[hypnotoad]

This is an abbreviated version. The problem is that the "Filter(" function will accept partial matches. I'm trying to determine a better way of making excel do something when the entered text is matched in the array. The userform will only allow certain entries, so error handling at this point is not needed.

Sub Test()

    Dim Array1()        As String
    Dim Array2()        As String
    Dim Array3()        As String
    Dim ArrayRange1     As Range
    Dim ArrayRange2     As Range
    Dim ArrayRange3     As Range
    Dim ListItem        As Range
    Dim Max             As Long
    Dim Acount          As Integer
        
    Set ArrayRange1 = ThisWorkbook.Sheets("List").Range("A2", ThisWorkbook.Sheets("List").Range("A" & Rows.Count).End(xlUp)).Cells
    Set ArrayRange2 = ThisWorkbook.Sheets("List").Range("B2", ThisWorkbook.Sheets("List").Range("B" & Rows.Count).End(xlUp)).Cells
    Set ArrayRange2 = ThisWorkbook.Sheets("List").Range("C2", ThisWorkbook.Sheets("List").Range("C" & Rows.Count).End(xlUp)).Cells
    
    Max = ThisWorkbook.Sheets("List").Range("A:A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array1(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange1
        Array1(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    Max = ThisWorkbook.Sheets("List").Range("B:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array2(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange2
        Array2(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    Max = ThisWorkbook.Sheets("List").Range("C:C").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array2(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange3
        Array3(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    
    UserForm1.Show      'Collects input from user
        Select Case UserForm1.Tag
            Case UBound(Filter(Array1, UserForm1.Tag)) >= 0
                'do something
            Case UBound(Filter(Array2, UserForm1.Tag)) >= 0
                'do something else
            Case UBound(Filter(Array3, UserForm1.Tag)) >= 0
                'do something else
        End Select

End Sub

 

[hypnotoad]

Perhaps a more accurate question would be: is there a way to use the filter command to search arrays for an exact match?

 

[hypnotoad]

Anyone have an idea on how to do this?

 

[Norie]

As far as I know Filter does to a search/filter on an exact match.

 

[pgc01]

Perhaps a more accurate question would be: is there a way to use the filter command to search arrays for an exact match?

Hi

You can use the match worksheet function to find an exact match in the array, like, for ex.:

Sub Test()
Dim l As Long
Dim vArr As Variant
 
vArr = VBA.Array("ABC", "AB", "A")
 
On Error Resume Next
l = Application.WorksheetFunction.Match("A", vArr, 0)
If l > 0 Then
    MsgBox "Found value """ & vArr(l - 1) & """ in position " & l
Else
    MsgBox "Value not found"
End If
End Sub

Does this help?

 

[hypnotoad]

pgc01,

Thanks for the great idea! I implemented that into the sub! I had to set separate variables and then test the variables to see which one was great than zero, but it works. The way I coded it feels sloppy to me though. Is there anyway I can embed it in the case statement? I tried the following as the cases but it gave me an error...

Select Case UserForm1.Tag
Case Application.WorksheetFunction.Match(UserForm1.Tag, Array1, 0) > 0
Case Application.WorksheetFunction.Match(UserForm1.Tag, Array2, 0) > 0
Case Application.WorksheetFunction.Match(UserForm1.Tag, Array3, 0) > 0

Current sub... that does work but feels sloppy.

Sub Test()

    Dim Array1()        As String
    Dim Array2()        As String
    Dim Array3()        As String
    Dim ArrayRange1     As Range
    Dim ArrayRange2     As Range
    Dim ArrayRange3     As Range
    Dim ListItem        As Range
    Dim Max             As Long
    Dim Acount          As Integer
    Dim i               As Long
    Dim j               As Long
    Dim k               As Long
    Dim Decision        As String
    
        
    Set ArrayRange1 = ThisWorkbook.Sheets("List").Range("A2", ThisWorkbook.Sheets("List").Range("A" & Rows.Count).End(xlUp)).Cells
    Set ArrayRange2 = ThisWorkbook.Sheets("List").Range("B2", ThisWorkbook.Sheets("List").Range("B" & Rows.Count).End(xlUp)).Cells
    Set ArrayRange2 = ThisWorkbook.Sheets("List").Range("C2", ThisWorkbook.Sheets("List").Range("C" & Rows.Count).End(xlUp)).Cells
    
    Max = ThisWorkbook.Sheets("List").Range("A:A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array1(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange1
        Array1(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    Max = ThisWorkbook.Sheets("List").Range("B:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array2(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange2
        Array2(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    Max = ThisWorkbook.Sheets("List").Range("C:C").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    ReDim Array2(1 To Max)
    Acount = 1
    For Each ListItem In ArrayRange3
        Array3(Acount) = ListItem.Value
        Acount = Acount + 1
    Next ListItem

    UserForm1.Show      'Collects input from user
    
        On Error Resume Next
            i = Application.WorksheetFunction.Match(UserForm1.Tag, Array1, 0)
            j = Application.WorksheetFunction.Match(UserForm1.Tag, Array2, 0)
            k = Application.WorksheetFunction.Match(UserForm1.Tag, Array3, 0)
        On Error GoTo 0
        
        If i > 0 Then
            Erase Array1, Array2, Array3
            Decision = "One"
         ElseIf j > 0 Then
             Erase Array1, Array2, Array3
             Decision = "Two"
         ElseIf k > 0 Then
             Erase Array1, Array2, Array3
             Decision = "Three"
        End If
        
        Select Case Decision
            Case "One"
                'do something
            Case "Two"
                'do something else
            Case "Three"
                'do something else
        End Select

End Sub

 

[pgc01]

Is there anyway I can embed it in the case statement?

Hi again

See if this example helps:

Sub Test()
Dim vArr1 As Variant, vArr2 As Variant, vArr3 As Variant
Dim sLookupValue As String, sOption As String
 
vArr1 = VBA.Array("ABC", "AB", "A")
vArr2 = VBA.Array("ABC", "AB", "B")
vArr3 = VBA.Array("ABC", "AB", "C")
 
sLookupValue = "B"
 
Select Case False
 
    Case IsError(Application.Match(sLookupValue, vArr1, 0))
        sOption = "One"
 
    Case IsError(Application.Match(sLookupValue, vArr2, 0))
        sOption = "Two"
 
    Case IsError(Application.Match(sLookupValue, vArr3, 0))
        sOption = "Three"
 
    Case Else
        sOption = "Not found"
 
End Select
 
MsgBox "Option: " & sOption
 
End Sub

 

[hypnotoad]

I had no idea you could do that with a case statement. Thank you for the input. I tried that code and it works (mostly). I'm unclear how to implement other things into a "select case false" statement, but I'll try! Thank you so much!