macro code error select case without end select...

T

Tonyk1051

Board Regular
Joined
Feb 1, 2021
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
VBA Code: 
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?..
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
You need always need an "End Select" statement

VBA Code: 
    Select Case Stage
    Case "DBM"
        cel.Offset(, 1) = "CLOSED"
        cel.Offset(, 2) = "CRD"

    Case "CRD"
        cel.Offset(, 1) = "CLOSED"
        cel.Offset(, 2) = "CRD"
    End Select
 
Upvote 0
rlv01 said:
You need always need an "End Select" statement

VBA Code: 
    Select Case Stage
    Case "DBM"
        cel.Offset(, 1) = "CLOSED"
        cel.Offset(, 2) = "CRD"

    Case "CRD"
        cel.Offset(, 1) = "CLOSED"
        cel.Offset(, 2) = "CRD"
    End Select
Click to expand...
I did that and add End Sub right after then i got another error saying for without next...
 
Upvote 0
Tonyk1051 said:
I did that and add End Sub right after then i got another error saying for without next...
Click to expand...
I can only go by what you post, and your code did not have an "end select". But often times code has more than one error , so when you fix one, then you get to see the next one. For each For statement, there must be a matching Next statement.

VBA Code: 
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 Select
    Next cel
End Sub
 
Upvote 0
Note that you can shorten your Select Case by combining cases that have the same actions:

VBA Code: 
    For Each cel In Rng
        stage = cel.Text
        Select Case stage
        Case "DBM", "CRD"
            cel.Offset(, 1) = "CLOSED"
            cel.Offset(, 2) = "CRD"

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

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

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

        Case "OUT", "NEW"
            cel.Offset(, 1) = "OPEN"
            cel.Offset(, 2) = "UNRESOLVED"
        End Select
    Next cel
 
Upvote 0
rlv01 said:
I can only go by what you post, and your code did not have an "end select". But often times code has more than one error , so when you fix one, then you get to see the next one. For each For statement, there must be a matching Next statement.

VBA Code: 
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 Select
    Next cel
End Sub
Click to expand...
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..
 
Last edited:
Upvote 0
basically in column K i have all the case words, if a select word is in column K,
rlv01 said:
Note that you can shorten your Select Case by combining cases that have the same actions:

VBA Code: 
    For Each cel In Rng
        stage = cel.Text
        Select Case stage
        Case "DBM", "CRD"
            cel.Offset(, 1) = "CLOSED"
            cel.Offset(, 2) = "CRD"

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

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

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

        Case "OUT", "NEW"
            cel.Offset(, 1) = "OPEN"
            cel.Offset(, 2) = "UNRESOLVED"
        End Select
    Next cel
Click to expand...

let me try this one and see if it works
 
Upvote 0
Tonyk1051 said:
this one got type error 13 mismatch, what does that mean
Click to expand...

Tonyk1051 said:
VBA Code: 
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?..
Click to expand...
 
Upvote 0
fds.xlsm not sure if it will help but heres a link to the macro file. i tried change the column format to text or general and its still the same error 13
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
259
Joe4
Joe4
U
  • Question
Information overwriting when transferring
Replies
7
Views
287
UTB
U
M
  • Solved
Copy and Paste Macro Based on Matching Values Not Doing Anything
Replies
2
Views
470
matt767
M
A
  • Question
Case without case select and else without if error
Replies
3
Views
265
Joe4
Joe4
R
  • Solved
SaveAs method not actually saving, wrong sheets being deleted (1004 Error)
Replies
4
Views
433
Alex Blakenburg
A

Forum statistics

Threads
1,215,455
Messages
6,124,938
Members
449,197
Latest member
k_bs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top