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?..

 

[mikerickson]

i did exactly how you had it and then another error...run time error "1004" method range of object _global failed...is the code i came up with completely wrong? the words im looking for are on column K header is "stage" and the case words are in column K, not sure what im doing wrong here..

The syntax for refering to all of column K is Range("K:K"), not the Range("K") posted in the OP.

 

[Tonyk1051]

The syntax for refering to all of column K is Range("K:K"), not the Range("K") posted in the OP.

thank you. the code works as intended but after it runs and puts the correct words into the corresponding cells it times out all the time. Is it due to the amount of lines i have. Its usually 45K to 400K lines....

 

[igold]

You could try something like this. The code assumes your data starts in Row 2

Sub t()
    Dim stage As String, arr, i As Long
    Worksheets("Page1").Activate
    
    arr = Range("K2:M" & Cells(Rows.Count, "K").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 "OUT", "NEW"
                arr(i, 2) = "OPEN"
                arr(i, 3) = "UNRESOLVED"
        End Select
    Next
    Range("K2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
End Sub

 

[rlv01]

When you say it "times out", what do you mean?

 

[Tonyk1051]

You could try something like this. The code assumes your data starts in Row 2

Sub t()
    Dim stage As String, arr, i As Long
    Worksheets("Page1").Activate
   
    arr = Range("K2:M" & Cells(Rows.Count, "K").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 "OUT", "NEW"
                arr(i, 2) = "OPEN"
                arr(i, 3) = "UNRESOLVED"
        End Select
    Next
    Range("K2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
   
End Sub

works perfect, thanks i just have rename the sheet to "page1" to make it work

 

[Michael M]

You also need a Next cel on the line following the End Select

End Select
Next cel

 

[Tonyk1051]

When you say it "times out", what do you mean?

it executes the command populating the next 2 empty columns with what i want but then the mouse arrow turns to the hour glass and it just stays stuck then becomes unresponsive... its okay. another member gave me a solution, regardless much appreciated and thank you

 

[igold]

You're welcome. I am sure that all members involved were happy to help. Thanks for the feedback.

 

[Tonyk1051]

You're welcome. I am sure that all members involved were happy to help. Thanks for the feedback.

works perfect, thanks i just have rename the sheet to "page1" to make it work

I need an add on to the code which should be executed after the initial code runs….
In column k if a cell has the word out and there is data in column O then put in column L closed. Thanks in advance

 

[igold]

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