Using Select Case in excel vba

Question

Just curious but is there any way to reduce this to smaller number of lines of code?

For k = 1 To 12
    Select Case k
        Case 1
            col = 9
        Case 2
            col = 10
        Case 3
            col = 11
        Case 4
            col = 12
        Case 5
            col = 13
        Case 6
            col = 14
        Case 7
            col = 15
        Case 8
            col = 16
        Case 9
            col = 17
        Case 10
            col = 18
        Case 11
            col = 19
        Case 12
            col = 20
    End Select
Next

Thanks!

Daniel · Accepted Answer · 2012-10-26 00:54:52Z

3

How about:

For k = 1 to 12
   col = k + 8
Next

Or what you were probably looking for:

For k = 1 To 12
    Select Case k
        Case 1 To 12
            col = k + 8
    End Select
Next

Here's the relevant MSDN, you can scroll down for examples.

edited Oct 26, 2012 at 0:54

answered Oct 26, 2012 at 0:46

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1775842 Over a year ago

Sweeeet!! I knew they had to be a more better way of looping. thanks a lot

Preet Sangha Over a year ago

might want to add an else clause for safety

Daniel Over a year ago

@PreetSangha Perhaps, but for this example it's really not needed. I considered it, but the OP wanted less lines and adding that would add no value in this circumstance.

Collectives™ on Stack Overflow

Using Select Case in excel vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related