Need help with select case

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

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

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

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))

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,

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