Page 227 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

Chapter 9

Because The Garden Company uses a consistent naming system for its products, you can cre­
ate a procedure to determine the category of each product ordered. In this naming system,
the first two characters of an item’s identification code indicate to which category the item
belongs (TL for Tools, SP for Supplies, and FN for Furniture). So, rather than require a user
to enter the category, the procedure could do it for them.

Note The data in the worksheet has all uppercase letters, so the entries in the proce­
dure’s Case statements look for uppercase-only category codes.

Public Sub NoteCategory()

Dim MyCell As Range
Dim strFirst, strLast, strAllCells, strCategory As String

strFirst = InputBox("Enter the address of the first cell in the OrderItem
column.")
strLast = InputBox("Enter the address of the last cell in the OrderItem column.")
strAllCells = strFirst & ":" & strLast

For Each MyCell In Range(strAllCells).Cells
Range(MyCe ll.Address).Select
strCategory = Left(MyCell.Value, 2)
Select Case strCategory
Case "TL"
ActiveCell.O ffset(0, 1).Value = "Tools"

201
Part 4 Advanced VBA