Select case statement using ranges in Excel

Dim WrkSheet As Excel.Worksheet

    ' Loop thru all the worksheets.
    For Each WrkSheet In ThisWorkbook.Worksheets

your macro

    Next

Sub TryThis()

Dim WrkSheet As Excel.Worksheet

i = 2

For Each WrkSheet In ThisWorkbook.Worksheets

Do Until Cells(i,5).Value = ""
            
Select Case Cells(i,5).Value


      Case 40179 To 40209
            Cells(i, 1).Value = "1-10"

      Case 20210 To 40237
         Cells(i, 1).Value = "2-10"

      Case 40238 To 40268
         Cells(i, 1).Value = "3-10"

      Case 40269 To 40298
         Cells(i, 1).Value = "4-10"

      Case 40299 To 40329
         Cells(i, 1).Value = "5-10"

      Case 40330 To 40359
         Cells(i, 1).Value = "6-10"

      Case 40360 To 40390
         Cells(i, 1).Value = "7-10"

      Case 40391 To 40421
         Cells(i, 1).Value = "8-10"

      Case 40422 To 40451
         Cells(i, 1).Value = "9-10"

      Case 40452 To 40482
         Cells(i, 1).Value = "10-10"

      Case 40483 To 40512
         Cells(i, 1).Value = "11-10"

      Case 40513 To 40543
         Cells(i, 1).Value = "12-10"

      Case 40544 To 40574
         Cells(i, 1).Value = "1-11"

      Case 40575 To 40602
         Cells(i, 1).Value = "2-11"

      Case 40603 To 40633
         Cells(i, 1).Value = "3-11"

End Select

i = i + 1

Loop

Next
End Sub

try something like this

Thomas

Sub FilterAndUpdate()
Dim sht As Worksheet, arr(1 To 15, 1 To 3) As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
arr(1, 1) = 20210
arr(2, 1) = 40179
arr(3, 1) = 40238
arr(4, 1) = 40269
arr(5, 1) = 40299
arr(6, 1) = 40330
arr(7, 1) = 40360
arr(8, 1) = 40391
arr(9, 1) = 40422
arr(10, 1) = 40452
arr(11, 1) = 40483
arr(12, 1) = 40513
arr(13, 1) = 40544
arr(14, 1) = 40575
arr(15, 1) = 40603
arr(1, 2) = 40237
arr(2, 2) = 40209
arr(3, 2) = 40268
arr(4, 2) = 40298
arr(5, 2) = 40329
arr(6, 2) = 40359
arr(7, 2) = 40390
arr(8, 2) = 40421
arr(9, 2) = 40451
arr(10, 2) = 40482
arr(11, 2) = 40512
arr(12, 2) = 40543
arr(13, 2) = 40574
arr(14, 2) = 40602
arr(15, 2) = 40633
arr(1, 3) = "2-10"
arr(2, 3) = "1-10"
arr(3, 3) = "3-10"
arr(4, 3) = "4-10"
arr(5, 3) = "5-10"
arr(6, 3) = "6-10"
arr(7, 3) = "7-10"
arr(8, 3) = "8-10"
arr(9, 3) = "9-10"
arr(10, 3) = "10-10"
arr(11, 3) = "11-10"
arr(12, 3) = "12-10"
arr(13, 3) = "1-11"
arr(14, 3) = "2-11"
arr(15, 3) = "3-11"

For Each sht In ActiveWorkbook.Worksheets
    For i = 1 To 15
        With sht.Cells(1, 1).CurrentRegion
            .AutoFilter field:=5, Criteria1:=">=" & arr(i, 1), Operator:=xlAnd, Criteria2:="<=" & arr(i, 2)
            If Application.WorksheetFunction.Subtotal(3, .Resize(, 1)) > 1 Then .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) = arr(i, 3)
            .AutoFilter
        End With
    Next
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Hi,

Try


Sub kTest()
    Dim i As Long, r As Long, a As String
   
    For i = 1 To Worksheets.Count
        With Worksheets(i)
            r = .Range("e" & .Rows.Count).End(xlUp).Row
            a = .Range("e1:e" & r).Address
            .Range("a1:a" & r).NumberFormat = "@"
            .Range("a1:a" & r).Value2 = Evaluate("=if(isnumber(" & a & "),text(" & a & ",""m-yy""),"""")")
        End With
    Next
End Sub


Kris

Smart pick Fernando, in that case, I'd recommend
=text(d7,"mm-yy")

T

IF your date is in D7 as example...
but as far as I see, you're checking in which mnth the date is, by comparing first and last day of each month with a specific data, and then showing m-yy in the column 1...
it's that simple !

nutsch, you're so right :-)

by the way, 20210 (mentioned in the question) was mistyped ...

Thanks Fernando!  That was great.  I've never used that fx before, but it's simple to understand.