Excel macro - select cases

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 B开发者_Go百科IG Select Case and not multiple Select Cases.

Many thanks!

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

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