Page 226 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

For Each MyCell In Range(strAllCells).Cells
If Len(MyCell.Value) < 10 Then
Range(MyCe ll.Address).Select
strContent s = MyCell.Value
intPadding = 10 - Len(MyCell.Value)
strPadding = Application.WorksheetFunction.Rept("0", intPadding)
MyCell.Num berFormat = "@"
MyCell.Value = st rPadding & strContents
End If

MyCell.Num berFormat = "@"
Next MyCell

End Sub

Caution You need to make sure that the order code you’re changing is stored as a string,
not as a number. If you add a string of zeros to the beginning of a cell value that Excel trans­
lates as a number (which includes a cell with a General format), Excel will discard the zeros
as meaningless. For a worksheet cell, change the cell’s format to Text.
Chapter 9
Returning the First or Last Several Characters from a String

When you work with spreadsheet data, it’s likely that you’ll find patterns in the data, perhaps
patterns that you yourself program in. Although it’s certainly possible that every character in
a string will serve a known purpose, you might just need to read in the first or last few char­
acters of a string to derive the information that you need for a particular task. For example,
if the first five digits of a book’s ISBN tell you the book’s publisher (and they do), you could
read those digits into memory, look up the publisher’s identity in a database or worksheet
table, and write the data into another cell programmatically.

To return the first or last several characters in a string, you use the Left function, which
returns characters from the beginning of a string, or the Right function, which returns char­
acters from the end of the string. The syntax of the two functions, with the exception of the
function name, of course, is identical.

Left(string, length)
Right(string, length)

For these functions, string is the variable or range that contains the string you want to process
and length is the number of characters you want to return. As an example, consider a work-
sheet where the items included in orders placed with The Garden Company are stored as a
collection of worksheet rows.

200
Part 4: Advanced VBA