PDA

View Full Version : [SOLVED] What's wrong with my "Select Case"



ValerieT
10-28-2013, 02:44 AM
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
10-28-2013, 03:35 AM
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
10-28-2013, 03:43 AM
I apologise and thanks you a lot

snb
10-28-2013, 03:58 AM
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
10-28-2013, 05:02 AM
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
10-28-2013, 05:28 AM
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
10-28-2013, 05:32 AM
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
10-28-2013, 06:14 AM
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
10-28-2013, 06:42 AM
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
10-30-2013, 06:23 AM
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.