SELECT CASE with several CASES

[gertie193]

Hello,

This is a continuation of a previous post, but I thought I should start a new thread since this is about SELECT CASE. (https://www.mrexcel.com/forum/excel...-consecutive-if-statements-use-if-elseif.html)

I'm now using SELECT CASE code to do 2 things: 1) when a user enters an ID in A2, the city will autopopulate in B2, and 2) the person's name will autopopulate in C2.

Question:
I have the code below to populate the city in B2, but can I simply put the other SELECT CASE statements underneath (see in bold) for the 2nd part of the code to populate the Person in C2?

I'm not questioning how to code b/c it works, but it just seems strange having consecutive SELECT CASE statements listed to autopopulate 2 fields on a worksheet simultaneously. I just wonder if there will be trouble later b/c of some sort of order of operations re: SELECT CASE to execute these 2 different parts of the code.

Thank you for your help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)'ID City Person code

Dim wkbk As Workbook
Dim wksht As Worksheet
Dim rID As Range
Dim rCity As Range
[B]Dim rPerson as Range[/B]


Set wkbk = ActiveWorkbook
Set wksht = ActiveSheet


Set rID = Range("A2")
Set rCity = Range("B2")
[B]Set rPerson = Range("C2")[/B]


'Clear B2 if A2 blank 
If rID.Value = "" Then
    rCity.Value = ""
End If


'For IDs starting with 1 letter 
Select Case Left(rID, 1) 'Case sensitive - <=====1st Select Case
    Case "N"
        rCity.Value = "Airville"
    Case "E"
        rCity.Value = "Aleive"
    Case "X"
        rCity.Value = "Hubble"
End Select


'For IDs starting with 2 letters
Select Case Left(rID, 2) '- <=====2nd Select Case
    Case "GP"
        rCity.Value = "Greens"
   Case "N1"
        rCity.Value = "Airtime"
End Select

[B]'For IDs starting with 2 letters[/B]
[B]Select Case rID.Value '- <=====1st Select Case for Person[/B]
[B]    Case "N" & "12345" To "N" & "42000"[/B]
[B]        rPerson.Value = "John Doe"[/B]
[B]    Case "N" & "42001" To "N" & "52000"[/B]
[B]        rPerson.Value = "James Smith"

    'Etc. for several more CASES
    
     Case Else
        Exit Sub[/B]
[B]End Select[/B]


End Sub

 

[gertie193]

I've never used SELECT CASE before, but I thought this through, and I don't see where there would be an issue populating B2 then C2 based on a value entered in A2 as the SELECT CODE is written above. The code is going to deal with each case to populate B2 then separately deal with each case to populate C2.

Unless someone sees any issues, I'm concluding this post.

Thank you.

 

[gertie193]

I did want to add that there will be a conflict if you add a Case Else in a 2nd Select Case like below.

[COLOR=#000000][FONT='inherit']Private Sub Worksheet_SelectionChange(ByVal Target As Range) [/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
Dim rID As Range
Dim rCity As Range
Dim rPerson As Range

Set rID = Range("A2")
Set rCity = Range("B2")
Set rPerson = Range("C2")

'Clear B2 and C2 if A2 blank
If rID.Value = "" Then
    rCity.Value = ""[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']   rPerson.Value = ""
Exit Sub
End If

'****TO POPULATE B2****

'For IDs starting with 1 letter - 20 count
Select Case Left(rID, 1) 'Case sensitive
    Case "N"
        rCity.Value = "Airline"
    Case Else
        rCity.Value = ""
End Select

'For IDs starting with 2 letters - 29 count
Select Case Left(rID, 2)
    Case "C1"
        rCity.Value = "Altuna"
   [B] 'Case Else 'commented out b/c it was contradicting the 1st Select Case
       'rCity.Value = ""[/B]
End Select

'****TO POPULATE C2****

Select Case rID.Value
    'Airline
    Case "N" & "00000" To "N" & "50000", "N50001", "N" & "50003" To "N" & "99999" 
       rPerson.Value = "Ed"
    Case "N50002"
       rPerson.Value = "Lou"
    Case Else
       rPerson.Value = ""
End Select

[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]