Page 230 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

The benefit of the SEARCH and FIND functions really comes to the fore when you combine
them with the MID function. Once you’ve used the SEARCH or FIND function to locate the
start of two consecutive fields, you can use the MID function to draw in the part of the string
you want. There is one more subtlety of which you need to be aware. Although the MID
function is part of the standard VBA package, the SEARCH and FIND functions are not, so
you’ll once again need to use the Application.WorksheetFunction object to call the functions,
as in the following example:

Application.WorksheetFunction.Search("IT", ActiveCell.Value)

Note The VBA function INSTR also returns the position of the character where a string
begins within another string, but the function is the equivalent of the FIND function in that
the INSTR function is case-sensitive.

If you reconsider the nightmare scenario where order item records were imported incor­
rectly, you could use the SEARCH and the MID functions to find the beginning and the end
of each field’s values and write the values into a cell, as in the following procedure:

Public Sub SeparateValues()
Dim MyCell As Range
Chapter 9
Dim intIT, intCI, intSP As Integer
Dim strFirst, strLast, strAllCells As String
strFirst = InputBox("Enter the address of the first cell.")
strLast = InputBox("Enter the address of the last cell.")
strAllCells = strFirst & ":" & strLast
For Each MyCell In Range(strAllCells).Cells

Range(MyCe ll.Address).Select
intIT = Appli cation.WorksheetFunction.Search("IT", MyCell.Value)
intCI = Appli cation.WorksheetFunction.Search("CI", MyCell.Value)
intSP = Appli cation.WorksheetFunction.Search("SP", MyCell.Value)
ActiveCell.Offse t(0, 2).Value = Mid(MyCell.Value, 1, intIT - 1)
ActiveCell.Offset( 0, 3).Value = Mid(MyCell.Value, intIT, intCI - intIT)
ActiveCell.Offset( 0, 4).Value = Mid(MyCell.Value, intCI, intSP - intCI)
ActiveCell.Of fset(0, 5).Value = Mid(MyCell.Value, intSP)

Next MyCell
End Sub

This procedure would take the strings in cells in the range entered by a user (A2:A21 in this
example) and write the component values into the cells to the right, as shown in Figure 9-2.

204
Part 4: Advanced VBA