VBA "Select Case" function, is this what I want to use?

[Tom Morris]

I'm developing a spreadsheet that take room names such as "Classroom
101" or "Storage", and
assigns to it a specific room type that is in turn used in a lookup
function.
Problem is, different users will enter in names differently, some will
abreviate "Clas. 101" or "Stor." for example.
One way in which I'm trying to save time from selecting the room type
from a drop down list is to
write a VBA program that will try to guess at what type of room the
user entered, I'm trying to use this structure:

Select Case Range ("A1").Text
Case "Cla" To "Clb"
Range ("B1").Value = "Classroom"
Case "Sto" To "Stp"
Range ("B1").Value = "Storage"
Case Else
Range ("B1").Value = "Select Room Type From Pull-Down Menu"
End Select

1) Is this a reasonable approach?
2) Is there not a way to look for any instance of the string pattern
"Cla" so that if a user enters "101 classroom", I can deal with that?
3) If this is a reasonable approach, what form will the "Range" object
form take so that I can loop through about 30 rows of rooms?

Thanks for Helping!!

Tom Morris

 

[Dave Peterson]

If I were only checking the first 3 characters, I'd do something like:

Select Case lcase(left(Range ("A1").Text,3))
Case "cla"
Range ("B1").Value = "Classroom"
Case "sto"
Range ("B1").Value = "Storage"
Case Else
Range ("B1").Value = "Select Room Type From Pull-Down Menu"
End Select

If I wanted to loop through a bunch of rows:

dim myCell as range
dim myRng as range

with worksheets("sheet99")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
select case lcase(left(mycell.value,3)))
case "cla" : mycell.offset(0,1).value = "Classroom"
case "sto" : mycell.offset(0,1).value = "Storage"
case else
mycell.offset(0,1).value = "Select Room Type From Pull-Down Menu"
end select
next mycell

You could use instr() to see if the string in the cell contains "clas".

If InStr(1, mycell.Value, "clas", vbTextCompare) > 0 Then
mycell.offset(0,1).value = "Classroom"
elseIf InStr(1, mycell.Value, "stor", vbTextCompare) > 0 Then
mycell.offset(0,1).value = "Storage"
else
mycell.offset(0,1).value = "Select Room Type From Pull-Down Menu"
end if

========
But if those were the only conditions, I might even consider just using a
formula:

=if(countif(a1,"*clas*")>0,"Classroom",
if(countif(a1,"*stor*")>0,"Storage",
"Select Room Type From Pull-Down Menu"))

(all one cell)

 

[(PeteCresswell)]

Per Tom Morris:

1) Is this a reasonable approach?.

Do Excel drop down lists have the AutoComplete feature that they have in MS
Access?

If so, having a bunch of drop downs might not be all that time-consuming for the
user. In fact, if the selections were sufficiently different; the user might
have an easier task. e.g. C ==> Class, S ==> Storage....

 

[tom]

Thank you very much Dave for your help.

 

[dbahooker]

dont use excel for data entry; it isn't up to par.

use a database like Access or something

-Aaron

 

[tom]

dont use excel for data entry; it isn't up to par.

use a database like Access or something

-Aaron

Are you serious?
You know nothing Aaron, about what I do, or how I should do it. Do yourself
a favor, get a clue someday.