What's wrong with my "Select Case"

[ValerieT]

I've read that "Select Case" is fasten than multiple 'If'. I try to use it, but even if I harcode the value of my test, it doesn't select it:

My_Position has been declared as string

 '    My_Position = Cells(Ici, CENT)
      My_Position = "AC"

      Select Case My_Position

             Case My_Position = "AC"
                  Sheets("Summary").Cells(Ou, 21) = Cells(Ici, CAWF1)
                  Sheets("NL").Cells(Ici, 5) = "AC"   ' Test

             Case My_Position = "AD"
                  Sheets("Summary").Cells(Ou, 18) = Cells(Ici, CAWF1)

             Case My_Position = "AF"
                  Sheets("Summary").Cells(Ou, 16) = Cells(Ici, CAWF1)

             Case My_Position = "AG"
                  Sheets("Summary").Cells(Ou, 19) = Cells(Ici, CAWF1)

             Case My_Position = "ME"
                  Sheets("Summary").Cells(Ou, 22) = Cells(Ici, CAWF1)

             Case Else
                  Sheets("NL").Cells(Ici, 5) = "ELSE"  ' Test

       End Select

The result always goes to "Else"

Function	Processing		Case (cells 5)	Entity (My_Position)
.	0.6			AC
.	1.6			AC
.	2.6			AC
.	3.6			AC
.	4.4			AC
.	5.4		ELSE	AC
B	1.0			AC
B	2.0		ELSE	AC

[snb]

Please read the VBEditor's helpfiles carefully.

My_Position = "AC" 

Select Case My_Position 
Case "AC" 
    Sheets("Summary").Cells(Ou, 21) = Cells(Ici, CAWF1) 
    Sheets("NL").Cells(Ici, 5) = "AC" ' Test
Case "AD" 
    Sheets("Summary").Cells(Ou, 18) = Cells(Ici, CAWF1) 
Case "AF" 
    Sheets("Summary").Cells(Ou, 16) = Cells(Ici, CAWF1) 
Case "AG" 
    Sheets("Summary").Cells(Ou, 19) = Cells(Ici, CAWF1) 
Case "ME" 
    Sheets("Summary").Cells(Ou, 22) = Cells(Ici, CAWF1) 
Case Else 
    Sheets("NL").Cells(Ici, 5) = "ELSE" ' Test
End Select

a simpler alternative instead of the 'select case' construction:

Sheets("Summary").Cells(Ou, 15+application.match(My_position,array("AF","", "AD","AG","","AC","ME"),0)= Cells(Ici, CAWF1)

[ValerieT]

I apologise and thanks you a lot

[snb]

Before applying the 'select case' construction you can analyse the conditions for any particular pattern.
Using that pattern can be simpler and more robust than the 'select case' method.

[Aflatoon]

a simpler alternative instead of the 'select case' construction:

Sheets("Summary").Cells(Ou, 15+application.match(array("AF","", "AD","AG","","AC","ME"))= Cells(Ici, CAWF1)

Not that simple - it doesn't make any sense. What is the one argument Match function supposed to be?

[ValerieT]

If I understood well, it increment +1, based on each element of the array on the right.

I can't use it in in my actual code (it doesn't show in my first post because I "simplified" it, but in fact I've got no regular pattern) but I am very glad to have learned it, and thankfull to snb who is always a great help.

[Aflatoon]

You misunderstood my point: that line of code doesn't work, or even compile. You can't have a match function with only one argument - it requires at least two (a value to match and a table/array to match against)

[Paul_Hossler]

To expand on SNB's solution ...

Select Case My_Position
    Case My_Position = "AC"

This actually is looking for a boolean value (My_Position = "AC") which is TRUE since you forced the AC to test


However, the Case selector is 'My_Position' which is a string

Select Case My_Position 
     
Case My_Position = "AC" 
    Sheets("Summary").Cells(Ou, 21) = Cells(Ici, CAWF1)

so what's happening is that the macro is looking to find a condition where

"AC" = True

This condition can never be met, so it always falls through to the 'Else' condition

Paul

[snb]

It 'appears' my suggestion needs some improvement:

Sheets("Summary").Cells(Ou, 15+application.match(My_position,array("AF","", "AD","AG","","AC","ME"),0)= Cells(Ici, CAWF1)

[jack nt]

the comparison "equal to" should be done with integer or similar numbers only! when you calculate with real numbers, there may be different in some bits and so it is never selected.