macro code error select case without end select...

[Tonyk1051]

Sub t()
Dim cel As Range, Rng As Range, stage As String
Worksheets("Page1").Activate
Range("K").Select
Set Rng = Range(Selection, Selection.End(xlDown))
For Each cel In Rng
stage = cel.Text
Select Case stage
Case "DBM"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "CRD"

Case "CRD"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "CRD"

Case "USE"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "USED"

Case "DSP"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "DSP"

Case "RTU"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "USED"

Case "RPL"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "STK"

Case "RTS"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "STK"

Case "RPN"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "STK"

Case "RPR"
cel.Offset(, 1) = "CLOSED"
cel.Offset(, 2) = "STK"

Case "OUT"
cel.Offset(, 1) = "OPEN"
cel.Offset(, 2) = "UNRESOLVED"

Case "NEW"
cel.Offset(, 1) = "OPEN"
cel.Offset(, 2) = "UNRESOLVED"


End Sub

when i try to run this macro i get an error saying compile error select case without end select. I dont know what that means...and im guessing what i created is completely wrong?..

 

[Tonyk1051]

Does this do what you want. Also, I did not realize that the previous code, which I stole the bulk of from another post here, was selecting the entire column, so I shortened that to the last row with data in column K.

Sub t()
    Dim stage As String, arr, i As Long
    Worksheets("Page1").Activate
   
    arr = Range("K2:M" & Cells(Rows.Count, "K").End(xlUp).Row)
    For i = 1 To UBound(arr)
        stage = arr(i, 1)
        Select Case stage
            Case "DBM", "CRD"
                arr(i, 2) = "CLOSED"
                arr(i, 3) = "CRD"
   
            Case "USE", "RTU"
                arr(i, 2) = "CLOSED"
                arr(i, 3) = "USED"
   
            Case "DSP"
                arr(i, 2) = "CLOSED"
                arr(i, 3) = "DSP"
   
            Case "RPL", "RTS", "RPN", "RPR"
                arr(i, 2) = "CLOSED"
                arr(i, 3) = "STK"
   
            Case "NEW", "OUT"
                arr(i, 2) = "OPEN"
                arr(i, 3) = "UNRESOLVED"
        End Select
    Next
    Range("K2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    arr = Empty
    arr = Range("K2:K" & Cells(Rows.Count, "K").End(xlUp).Row)
    For i = 1 To UBound(arr)
        If arr(i, 1) = "OUT" And Cells(i + 1, 15) <> "" Then Cells(i + 1, 12).Value = "CLOSED"
    Next
End Sub

The code is golden, thank you sir

 

[igold]

You're welcome.