• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select Case with multiple ranges

inkserious

New Member
Hello,


Is it possible to use select case to evaluate the value in a ComboBox against a range? I have six named ranges. One range has 30 three digit values in it. The other five ranges contain several of the numbers in the first range. I've posted some code below to illustrate what I'm trying to achieve. It doesn't work in it's current state. I hope someone can help out with this one.


Thanks.


-ep


'Private Sub cboTable_Change()


Dim tNum As Range

Dim g As String

Dim aTables As Range

Dim ws As Worksheet


Set ws = Worksheets("Sheet3")


Set aTables = ws.Range("allTables")


For Each tNum In aTables

Select Case tNum.Value


Case ws.Range("bjTables"): g = "BJ" 'if tNum.Value = any of the values in the range "bjTables" then "BJ"

Case ws.Range("crTables"): g = "CR"

Case ws.Range("roTables"): g = "RO"

Case ws.Range("ppTables"): g = "PP"

Case ws.Range("msTables"): g = "MS"

Case Else: g = ""


End Select

Next tNum


Me.cboGames.Value = g


End Sub`
 
I think you would need someway to loop through each table. I marked it in the code too, but do note that currently you are not doing anything with g until you exit the For loop. I'm assuming that this is an error and you wanted to do something with each g that is found?

[pre]
Code:
Private Sub cboTable_Change()

Dim tNum As Range
Dim g As String
Dim aTables As Range
Dim bjTables As Range
Dim crTables As Range
Dim roTables As Range
Dim ppTables As Range
Dim msTables As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet3")

Set aTables = ws.Range("allTables")
Set bjTables = ws.Range("bjTables")
Set crTables = ws.Range("crTables")
Set roTables = ws.Range("bjTables")
Set ppTables = ws.Range("ppTables")
Set msTables = ws.Range("msTables")

For Each tNum In aTables
g = ""
For Each c In bjTables
If c = tNum.Value Then
g = "BJ"
GoTo EntryFound
End If
Next c
For Each c In crTables
If c = tNum.Value Then
g = "CR"
GoTo EntryFound
End If
Next c
For Each c In roTables
If c = tNum.Value Then
g = "RO"
GoTo EntryFound
End If
Next c
For Each c In ppTables
If c = tNum.Value Then
g = "PP"
GoTo EntryFound
End If
Next c
For Each c In msTables
If c = tNum.Value Then
g = "MS"
GoTo EntryFound
End If
Next

EntryFound:
'Should something be done with g before going onto next tNum?
'(see below)

Next tNum

'otherwise, this will only pick up the last g value
Me.cboGames.Value = g

End Sub
[/pre]
 
Thanks Luke M. Sorry for the delay in responding. I'm still not getting the expected results. Perhaps a better explanation of what I'm trying to achieve will determine if it's even possible. cboGames contains a range containing the following: "BJ", "CR", "RO", "PP", "MS". Each one of those strings correlates to a range: bjTables, crTables, etc... The latter ranges are displayed in cboTables. So each game in cboGames has certain tables assigned to it in cboTables. Change the game, get a new range of tables. I am able to get that part working. The way data is entered into the form is that table is entered before game. So if the user could type a table number in and have the game displayed that would be helpful. The problem is if the user types in a table in cboTable, then the game changes in cboGames, which in turn attempts to change the range in cboTable.


Please let me know if this is possible, or if you have any other suggestions.


Thanks again.


-ep
 
Hi ,


Can you let me know if my understanding of your question is correct ?


1. A named range "cboGames" can contain any one of "BJ" , "CR", "RO", "PP", "MS".


2. You also have named ranges "bjTables" , "crTables" ,..., "msTables".


3. A named range "cboTables" can contain any one of "bjTables" , "crTables" ,..., "msTables".


4. In your Select Case statement , Select Case tNum.Value , tNum.Value will be one of "bjTables" , "crTables" ,..., "msTables".


5. You should now be comparing tNum.Value with the strings themselves instead of ws.Range("bjTables") , ws.Range("crTables") ,..., ws.Range("msTables").


Narayan
 
One trick that I use with Select Case statements is to use "Select Case True" (see below). This allows you to sequentially process logic statements (similar to nested IF-THEN-ELSE statements, but with more flexibility and better readability).


Select Case True

Case logic1

' do something

Case logic2

' do something

Case logic3

'do something

Case Else

'do something else

End Select


So, in your case, try something like:


Select Case True

Case WorksheetFunction.CountIf(Range("bjTables"),"=" & tNum.Value) > 0

g="BJ"

Case WorksheetFunction.CountIf(Range("crTables"),"=" & tNum.Value) > 0

g="CR"

etc.


Case Else


End Select


HTH,

NukeRiskGuy
 
Back
Top