Noob: Case select & Statement

**dohara** · 04-28-2014, 05:49 PM

I have a case select that I use in another spreadsheet. I am trying to edit it to work for my current one. What I need to do is add an & statement. Meaning if C2 Case Is < 46.1 & D2 Case Is < 6 arrStore(StoreIndex) = "A"

Currently this is what I have

Sub SortIntoGroups()
    Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

    With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case varZip
                Case Is < 46.1
                    arrStore(StoreIndex) = "A"
                Case 46 To 100
                    arrStore(StoreIndex) = "B"
                Case Is > 100.1
                    arrStore(StoreIndex) = "C"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With

    If StoreIndex > 0 Then Range("F2").Resize(StoreIndex).Value = Application.Transpose(arrStore)


End Sub

**AlphaFrog** · 04-28-2014, 06:49 PM

Try something like this...

Select Case True
    Case varZip < 46.1 And varZip.Offset(, 1) < 6
        arrStore(StoreIndex) = "A"

NOTE: varZip is a Range type object if you want to declare it as a Range instead of Variant

**dohara** · 04-28-2014, 07:00 PM

I tried below, and have an issue. It is stopping at the first case. I like where you were going with it.

Sub SortIntoGroups()
    Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

    With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case varZip
           Case Is <= 55 And varZip.Offset(, 1) < 6[/COLOR]
        arrStore(StoreIndex) = "A"
                Case Is <= 63 And varZip.Offset(, 1) = 6, 7
                arrStore(StoreIndex) = "B"
                Case 55.1 To 500 And varZip.Offset(, 1) < 5
                    arrStore(StoreIndex) = "B"
                Case Is >= 77 And varZip.Offset(, 1) = 8, 9
                    arrStore(StoreIndex) = "C"
                Case 63.1 To 500 And varZip.Offset(, 1) = 6, 7
                    arrStore(StoreIndex) = "C"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With
    
   

    If StoreIndex > 0 Then Range("F2").Resize(StoreIndex).Value = Application.Transpose(arrStore)
   End Sub

**AlphaFrog** · 04-28-2014, 07:27 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Edit your previous posts and add code tags.

This is the code you edited

Select Case varZip
    Case Is <= 55 And varZip.Offset(, 1) < 6
        arrStore(StoreIndex) = "A"

Which is not like the code I suggested.

Select Case True
    Case varZip < 46.1 And varZip.Offset(, 1) < 6
        arrStore(StoreIndex) = "A"

**tom.hogan** · 04-28-2014, 09:16 PM

You can't use multiple variables in select case unless you use the select case true construct as shown by alpha frog.

Tom

**dohara** · 04-28-2014, 10:06 PM

I realize it is not exactly what you wrote as I had to edit it. I am having the same issue. I am sure you can see what I need it to do. What is the issue?

Sub Sort()

    Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

    With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case True
                Case varZip <= 55 And varZip.Offset(, 1) < 6
                     arrStore(StoreIndex) = "1"
                Case varZip <= 63 And varZip.Offset(, 1) = 6, 7
                     arrStore(StoreIndex) = "2"
                Case varZip = 55.1 To 500 And varZip.Offset(, 1) < 6
                     arrStore(StoreIndex) = "2"
                Case varZip <= 77 And varZip.Offset(, 1) = 8, 9
                     arrStore(StoreIndex) = "3"
                Case varZip = 63.1 To 500 And varZip.Offset(, 1) = 6, 7
                     arrStore(StoreIndex) = "3"
                Case varZip = 77.1 To 500 And varZip.Offset(, 1) = 8, 9
                     arrStore(StoreIndex) = "4"
                Case varZip = 77.1 To 111 And varZip.Offset(, 1) = 10, 11
                     arrStore(StoreIndex) = "4"
                 Case varZip <= 77 And varZip.Offset(, 1) = 10, 11
                     arrStore(StoreIndex) = "5"
                 Case varZip = 111.1 To 500 And varZip.Offset(, 1) = 10, 11
                     arrStore(StoreIndex) = "6"
                 Case varZip = 111.1 To 500 And varZip.Offset(, 1) = 12, 13, 14, 15, 16, 17, 18
                     arrStore(StoreIndex) = "6"
                 Case varZip <= 111.1 And varZip.Offset(, 1) = 12 To 18
                     arrStore(StoreIndex) = "7"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With
    
   

    If StoreIndex > 0 Then Range("I2").Resize(StoreIndex).Value = Application.Transpose(arrStore)
   End Sub

**AlphaFrog** · 04-28-2014, 11:05 PM

Thank you for the code tags. Well done.

Try something like this. It's not tested and I don't know if I got all your logic correct, but you should get a sense of the syntax used.

Sub Sort()
    
    Dim c As Range, d As Range
    Dim arrStore() As String
    Dim StoreIndex As Long
    
    With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each c In .Cells
            Set d = c.Offset(, 1)
            StoreIndex = StoreIndex + 1
            Select Case True
                Case c <= 55 And d < 6
                     arrStore(StoreIndex) = "1"
                Case (c <= 63 And (d = 6 Or d = 7)) Or _
                     (c > 55 And c <= 500 And d < 6)
                     arrStore(StoreIndex) = "2"
                Case (c <= 77 And (d = 8 Or d = 9)) Or _
                     (c > 63 And c <= 500 And (d = 6 Or d = 7))
                     arrStore(StoreIndex) = "3"
                Case (c > 77 And c <= 500 And (d = 8 Or d = 9)) Or _
                     (c > 77 And c <= 111 And (d = 10 Or d = 11))
                     arrStore(StoreIndex) = "4"
                Case c <= 77 And (d = 10 Or d = 11)
                     arrStore(StoreIndex) = "5"
                Case (c > 111 And c < 500 And (d = 10 Or d = 11)) Or _
                     (c > 111 And c < 500 And d >= 12 And d <= 18)
                     arrStore(StoreIndex) = "6"
                Case c <= 111 And d >= 12 And d <= 18
                     arrStore(StoreIndex) = "7"
                Case c = ""
                     arrStore(StoreIndex) = ""
                Case Else
                     arrStore(StoreIndex) = "none"
            End Select
        Next c
    End With
    
    If StoreIndex > 0 Then Range("I2").Resize(StoreIndex).Value = Application.Transpose(arrStore)
End Sub

**dohara** · 04-28-2014, 11:18 PM

Perfect! It worked. I cam up with one that was a lot more work, complicated and a pain in the rear. I'm going to stick with yours! Thank you very much!!!!!!!

If you would by chance take a look at my other thread to see if you can help point me in a direction. Thanks.
http://www.excelforum.com/excel-prog...roup-noob.html

Also, here is what I did on my own before you got yours to work, just so you can see how much you helped me out.

Sub sorttest()
    Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

    With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case varZip
                Case Is <= 55
                    arrStore(StoreIndex) = "1"
                Case 55.1 To 63
                    arrStore(StoreIndex) = "2"
                Case 63.1 To 77
                    arrStore(StoreIndex) = "3"
                Case 77.1 To 111
                    arrStore(StoreIndex) = "4"
                Case 111.1 To 500
                    arrStore(StoreIndex) = "5"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With
    
   

    If StoreIndex > 0 Then Range("F2").Resize(StoreIndex).Value = Application.Transpose(arrStore)
   End Sub
Sub sorttest2()
   Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

With Range("D2", Cells(Rows.Count, "D").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case varZip
                Case Is <= 5
                    arrStore(StoreIndex) = "1"
                Case 6 To 7
                    arrStore(StoreIndex) = "2"
                Case 8 To 9
                    arrStore(StoreIndex) = "3"
                    Case 10 To 11
                    arrStore(StoreIndex) = "4"
                    Case 12 To 18
                    arrStore(StoreIndex) = "5"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With

    If StoreIndex > 0 Then Range("G2").Resize(StoreIndex).Value = Application.Transpose(arrStore)


End Sub

Sub sorttest3()
Dim varZip As Variant
    Dim arrStore() As String
    Dim StoreIndex As Long

With Range("H2", Cells(Rows.Count, "H").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrStore(1 To .Rows.Count)
        For Each varZip In .Value
            StoreIndex = StoreIndex + 1
            Select Case varZip
                Case Is = 11
                    arrStore(StoreIndex) = "A"
                Case Is = 12, 22, 21, 31, 41, 51
                    arrStore(StoreIndex) = "B"
                Case Is = 13, 23, 33, 32, 42, 52
                    arrStore(StoreIndex) = "C"
                    Case Is = 43, 53, 44, 53
                    arrStore(StoreIndex) = "D"
                    Case Is = 14, 24, 34
                    arrStore(StoreIndex) = "E"
                    Case Is = 54, 55
                    arrStore(StoreIndex) = "F"
                    Case Is = 15, 25, 35, 45
                    arrStore(StoreIndex) = "G"
                Case ""
                    arrStore(StoreIndex) = ""
                Case Else
                    arrStore(StoreIndex) = "none"
            End Select
        Next varZip
    End With

    If StoreIndex > 0 Then Range("I2").Resize(StoreIndex).Value = Application.Transpose(arrStore)


End Sub


Sub Clear()
    Range("F2:F500,G2:G500,I2:I500").Select
    Range("F5").Activate
    Selection.ClearContents
    Range("A4").Select
End Sub

Noob: Case select & Statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Re: Noob: Case select & Statement

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Utilize a Select Case Statement in Target Intersect Statement

Select Case Statement

Select Case Statement

Select Case Statement Help

select case statement

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions