Defining Ranges

You can also use the Insert method to add a specific value to a cell. In order to insert a value in a cell you need to use the Insert method with the Characters object. You can insert a string of characters at the beginning of the characters in a cell, or at any location within the cell. For example, to insert the string "New String" in cell B1 and replace the contents you type the following code:

Example:

Range("B1").Characters.Insert("New String")

If you want to place the new string within the existing string of characters, you must indicate the character location to place the new string, and the number of characters to replace at that location. For example, if you have the string "Excel 2000 Worksheet" you can replace the "2000" in the string with "2002" using the Insert method. The following code illustrates how to replace the portion of the string when the string is located in cell A1:

Example:

Range("A1").Characters(7,4).Insert("2002")

The Characters object has two parameters, Start and Length. The Start parameter indicates the number of the character to start the insert, in this case character 7. The Length parameter indicates the number of characters to replace in the string.

Q Type CopyOrigin:=True.

■ Excel copies the specified range of cells and inserts a copy as a new range in the worksheet.

< Type Range("A1:B2").Insert, replacing Range("A1:B2") with the range where you want to add cells.

'-Q Type Shift:=xlShiftDown, replacing xlShiftDown with the constant value indicating how cells should shift.

Q Type CopyOrigin:=True.

□ Switch to Excel and run the macro.

■ Excel copies the specified range of cells and inserts a copy as a new range in the worksheet.

SET THE WIDTH OF COLUMNS IN A RANGE

You can customize the width of a column using the ColumnWidth property. With this property, you specify how wide the specific column displays when you view the worksheet containing the column in Excel. By default, Excel assigns a width of 8.43 characters to each column. Excel bases this width size upon the number of zeros it can place in the cell using the default font style, which is the Normal font style. Excel bases this measurement upon the number of zeros, not characters, that it can place within the cell and still have them visible. Because most fonts that you use within Excel are proportional fonts, the spacing varies based upon each character. For example, you can always fit more of the letter "I" in a cell than you can of the letter "M" when working with a proportional font. When you use a monospaced font, such as Courier, the width is an actual measurement of the number of characters that fit in the column because, with this font, all characters require the same amount of space. Keep in mind, numeric digits 0 through 9 are all the same width regardless of whether you use a proportional or monospaced font.

You can use the ColumnWidth property to determine the width of the columns in a range. If all columns in the range have the same width, the width is returned as the number of characters that can display in each column. If the columns within the selected range do not have the same width, a value of Null is returned.

SET THE WIDTH OF COLUMNS IN A RANGE

SET THE WIDTH OF COLUMNS IN A RANGE

□ Create a new subroutine.

< Type Dim NumColumns As Integer, replacing NumColumns with the name of the variable containing the number of columns.

Type Set NewRange = Range("B1:D21"), replacing Range("B1:D21") with the range of cells.

Q Type NumColumns = NewRange.Columns.Count.

□ Create a new subroutine.

L0 Type Dim NewRange As Range, replacing NewRange with the name of the range of cells.

< Type Dim NumColumns As Integer, replacing NumColumns with the name of the variable containing the number of columns.

Type Set NewRange = Range("B1:D21"), replacing Range("B1:D21") with the range of cells.

Q Type NumColumns = NewRange.Columns.Count.

0 0

Post a comment