Excel VBA: Select Case Statements Conflicting with Each Other? - Microsoft Community

**edited move office programming category**

i'm using select case code 2 things: 1) when user enters id in a2, city autopopulate in b2, , 2) person's name autopopulate in c2.

question is, i have code below populate city in b2 , person in c2. works if comment out part below:

'for ids starting 2 letters - 29 count
select case left(rid, 2)
    case "c1"
        rcity.value = "altuna"
    case else
        rcity.value = ""
end select

there won't error, word 'airline' flash in cell b2 wants populate (like should), disappears. 'ed' or 'lou' appear in cell c2 should, though.

why there conflict due code above? there not mention of id starting 'n' conflict code above (see below):

'for ids starting 1 letter - 20 count
select case left(rid, 1) 'case sensitive
    case "n"
        rcity.value = "airline"
    case else
        rcity.value = ""
end select

i appreciate help. thank you.

it's first time use select case.

~~~~~~  

sub test()

dim rid range
dim rcity range
dim rperson range


set rid = range("a2")
set rcity = range("b2")
set rperson = range("c2")

'clear b2 if a2 blank
if rid.value = "" then
    rcity.value = ""
    exit sub
end if

'****to populate b2****

'for ids starting 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 2 letters - 29 count
select case left(rid, 2)
    case "c1"
        rcity.value = "altuna"
    case else
        rcity.value = ""
end select


'****to populate c2****

select case rid.value
    'airline
    case "n" & "00000" "n" & "50000", "n50001", "n" & "50003" "n" & "99999" 
       rperson.value = "ed"
    case "n50002"
       rperson.value = "lou"
    case else
       rperson.value = ""
end select

end sub

for id, i'd suggest like:

if isnumeric(mid(rid, 2, 1)) 'this single-alpha id, because second character numeric , not letter

'for ids starting 1 letter - 20 count

... stuff

else

'for ids starting 2 letters - 29 count

... stuff

endif

this minimal code, no error checking things errant id 1 character long, etc.

Office / Excel / Microsoft Office Programming / Office 2013