asked on

Excel case statements

I have around 800 Silicone bands with RFID chips in them. I have to attach a name to each chip number - so;
chip number, first and last name, class, team, age this year. I intend to put the data into an excel spreadsheet. I am using a reader to read the chips into Excel. As the number is read in Excel automatically moves to the next cell below. I want the other data to populate the cells next to the number. I was going to use VB case statements for each of the band numbers. For example;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range, TestRange As Range
Set myRange = Range("E2:E40")
Set TestRange = Intersect(Target, myRange)
If TestRange Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Value
Case 1234
Target.Offset(0, 1).Value = "Jill Smith"
Target.Offset(0, 2).Value = "Year 3"
Target.Offset(0, 2).Value = "Flinders"
Target.Offset(0, 2).Value = IF(C2="","", TEXT(TODAY() - C2,"yy""years""mm""months""))

That is a lot of cases. Is there a shorter or better way to do this in Excel?

ASKER CERTIFIED SOLUTION

CompProbSolv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dier Eluom

ASKER

Thank you. I had thought of using those but had wondered if the VB approach with cases would have been better. No matter, I will use the Hlookup.

CompProbSolv

I think that Case is fine when you have a small number of fixed choices. As you are seeing, it gets ugly when you have a lot of choices, let alone what it takes to make it work when there are new choices.