MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with select case


Posted by Emma on January 06, 2002 8:58 AM

How would I write so that a select case runs only on that area which I have selected, quantity and location of cells will vary.

For Example,
If "first cell in selection" contains the string "dev" Then the cell in the column immediately before it should be 17 else
If it contains "pro" then 11 else
If it contains "admin" then 20 else
So on and so forth...
Next cell

Thanks :-)


Posted by Damon Ostrander on January 06, 2002 10:31 PM

Hi Emma,

This example code would be:

Set Previous = Selection.Cells(1).Offset(0,-1)
Select Case Selection.Cells(1)
Case "dev": Previous = 17
Case "pro": Previous = 11
Case "admin": Previous = 20
Case Else: Previous = 0
End Select

The colons are so that the "Previous =" statement can be on the same line.

Happy computing.

Damon


Posted by Emma on January 07, 2002 3:54 AM

Hi Damon,

Can this code be modified so that the selected cells need only "contain" the string in question? For example, the cell may contain :developer or dev or develop as opposed to just dev.

I pasted this code into a workbook to test it and I must be doing something wrong.

Ignoring the above, I entered the strings in question, dev, pro & admin, into cells B1, B2 & B3 respectively. I then selected them and ran the macro. Nothing happens. The "contain" problem aside, what am I doing wrong?

Thanks,
Emma


Posted by Bariloche on January 07, 2002 5:23 AM

Emma,

Just real quick ('cuz I gotta get to the day job), if you just copied that snippet from Damon "as is" and then selected three cells as you describe, the thing that is "missing" is a loop through your selection.

I'd use the following:

Dim cell as Range 'put this in the declarations

For Each cell In Selection

(Select Case code from Damon goes here)

Next cell

Now then, since we haven't seen your code, I can't know if this is the absolutely correct approach to what you are doing. But given your question, this is a solution. Note: it may involve some modification of Damon's code.

Re: Like "dev". I'd recommend that you use something like:

Select Case UCase(Selection.Cells(1))
Case Is Like "DEV"
... etc.

Something along those lines should work. (Unfortunately I don't have the time to "debug" the syntax, this should get you headed in the right direction and perhaps someone else can tweak it if need be.)


have fun


Posted by Emma on January 07, 2002 6:39 AM

Need more help with select case

Can anyone modify this for me so that I do not get an error on the "Cell As Range" line and so that the cell being searched can simply contain "dev" as opposed to be "dev"? Thanks


Set Previous = Selection.Cells(1).Offset(0, -1)
Cell As Range
For Each Cell In Selection
Select Case UCase(Selection.Cells(1))
Case "Dev": Previous = 17
Case "pro": Previous = 11
Case "admin": Previous = 20
Case Else: Previous = 0
End Select
Next Cell Emma, Just real quick ('cuz I gotta get to the day job), if you just copied that snippet from Damon "as is" and then selected three cells as you describe, the thing that is "missing" is a loop through your selection. I'd use the following: Dim cell as Range 'put this in the declarations For Each cell In Selection (Select Case code from Damon goes here) Next cell Now then, since we haven't seen your code, I can't know if this is the absolutely correct approach to what you are doing. But given your question, this is a solution. Note: it may involve some modification of Damon's code. Re: Like "dev". I'd recommend that you use something like: Select Case UCase(Selection.Cells(1))


Posted by Damon Ostrander on January 07, 2002 8:21 AM

Re: Need more help with select case

Hi Emma,

I apologize for misinterpreting your question before. I didn't realize you wanted it to loop through all cells in the selection. Here is the corrected code. Bariloche was definitely on the right track, except that unfortunately the Like operator is not allowed with the Case statement. As a result, you must use the If Then ElseIf... instead of the Select Case. Using the UCase was also a good idea if you don't want the test to be case-sensitive. For this to work the test strings (e.g. "DEV") MUST be upper case. Note that the strings have "*" front and back to allow the Like operator to find the target string anywhere in the cell.

Happy computing.

Damon

Dim Cell As Range
Dim Previous As Range

For Each Cel In Selection

Set Previous = Cel.Offset(0, -1)

If UCase(Cel) Like "*DEV*" Then
Previous = 17
ElseIf UCase(Cel) Like "*DEV*" Then
Previous = 17
ElseIf UCase(Cel) Like "*PRO*" Then
Previous = 11
ElseIf UCase(Cel) Like "*ADMIN*" Then
Previous = 20
End If

Next Cel Can anyone modify this for me so that I do not get an error on the "Cell As Range" line and so that the cell being searched can simply contain "dev" as opposed to be "dev"? Thanks

: Hi Damon,


Posted by Emma on January 07, 2002 8:36 AM

Works great!! Thanks a million!! :-)

I apologize for misinterpreting your question before. I didn't realize you wanted it to loop through all cells in the selection. Here is the corrected code. Bariloche was definitely on the right track, except that unfortunately the Like operator is not allowed with the Case statement. As a result, you must use the If Then ElseIf... instead of the Select Case. Using the UCase was also a good idea if you don't want the test to be case-sensitive. For this to work the test strings (e.g. "DEV") MUST be upper case. Note that the strings have "*" front and back to allow the Like operator to find the target string anywhere in the cell. Dim Cell As Range If UCase(Cel) Like "*DEV*" Then Previous = 17 ElseIf UCase(Cel) Like "*DEV*" Then Previous = 17 ElseIf UCase(Cel) Like "*PRO*" Then Previous = 11 ElseIf UCase(Cel) Like "*ADMIN*" Then Previous = 20 End If Next Cel : Can anyone modify this for me so that I do not get an error on the "Cell As Range" line and so that the cell being searched can simply contain "dev" as opposed to be "dev"? Thanks : Set Previous = Selection.Cells(1).Offset(0, -1) Cell As Range For Each Cell In Selection Select Case UCase(Selection.Cells(1)) Case "Dev": Previous = 17 Case "pro": Previous = 11 Case "admin": Previous = 20 Case Else: Previous = 0 End Select Next Cell