Excel macro - select cases

Question

I don't anyone has posted this question yet, don't shoot me if I'm wrong! :P

I have a monster if else structure of about 300-400 lines. With a case structure I'd be able to cut it down to 100 or less. Trouble is you only find select cases with fixed values and not cell values.

What does the code do?

C17-C22 can be given a value, 1 or 0. If the value is 1 a text is copied into a worksheet, if the value is 0 nothing happens.

Select case ???
   Case "C17" = 1
      show me text!!
   Case "C18" = 1
      show me text!!
   ...
End select

I know I can do it with different select cases

Select case "C17".value
   Case 1
      show me text!!
End select
...

But I think it should be possible to do it in one BIG Select Case and not multiple Select Cases.

Many thanks!

What does each of the case do? Can you give example code for anyone to understand the overall picture? — shahkalpesh
– shahkalpesh, Commented May 2, 2011 at 7:33
@Shahk, I have edited my code slightly. Each case just copy/pastes text, but I know how to do this. — CustomX
– CustomX, Commented May 2, 2011 at 7:38

Jean-François Corbett · Accepted Answer · 2011-05-02 11:31:21Z

1

It sounds like you're making your own life more complicated by trying to shoehorn your problem into a Select Case construct. Select Case, by the way, is almost exactly the same as If Else; the difference is mainly cosmetic and you could in principle use them interchangeably.

Why not something like this:

Dim i As Long
Dim varFlags As Variant
Dim varText As Variant

' Load flags from sheet.
varFlags = Range("C17:C22")

' Load text associated with each flag. Here I'll just hardcode them. 
varText = Array("Text17", "Text18", "Text19", "Text20", "Text21", "Text22")

For i = LBound(varFlags, 1) To UBound(varFlags, 1)
    If varFlags(i, 1) = 1 Then
        'It's a 1. Copy the relevant text. 
        'Here I just MsgBox it.
        MsgBox varText(i - 1) 'Sorry for the i-1, my varText array is zero-based!
    Else
        'It's not a 1. Do nothing.
    End If
Next i

edited May 2, 2011 at 11:31

answered May 2, 2011 at 11:23

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

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

Comments

stema · Accepted Answer · 2011-05-02 08:04:50Z

1

The Select Statement is to distinguish between some different values a variable can be. I.e. Select Case is the left side of the equation and the following case Statments are the different right sides of the equation. I think your task is not a job for Select case

If the cases are the same for every cell, loop through your range and do the select for every cell. But if you only check if the cell is 0 or 1 then use an if

Something like:

For each c in SourceRange
  if c.value Then
      ...
  end if
Next c

answered May 2, 2011 at 8:04

stema

93.5k20 gold badges110 silver badges138 bronze badges

2 Comments

CustomX Over a year ago

hmm, I think you misunderstood my question. The text shown with each case is different. Using a For each loop is only suitable for 1 case, so I would need 5 For Each loops. For a outsiders, I think the select case structure is easier to understand.

stema Over a year ago

You can also put your select Statement into the For each and do 5 things for each cell. If the task is basically the same for each cell, then don't do the same job again and again. Of course you can copy for each cell a different text into a different place of the worksheet.

Collectives™ on Stack Overflow

Excel macro - select cases

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related