How to access values and manipulate cells in a cell with VBA code in Excel 2003-Columns of Tang and song ci-Blog Channel-csdn.net

When you write VBA code in Excel, the most common thing you can do is manipulate the data in the cells in the form. I'll summarize here how to manipulate the data in a cell from VBA code.

The Range object is required to manipulate cells in VBA code, and range is a class provided by the Excel library (that is, the Excel.exe file), encapsulating all operations on cells in the form. A Range object can be a cell, a row of cells, a column of cells, or a contiguous range of cells in a square, or even a combination of several cell ranges. As to what a specific Range object really stands for, it depends on how we construct it. (Note that the range class does not support the new operator, and the range variable declared in VBA code can only be a reference to an existing Range object.) )

When assigning a value to a range variable in Excel VBA, the left side of the equals sign is the reference name of a Range object, and to the right is a property or method that returns a Range object. Some properties that can return a Range object reflect the polymorphism of the (constructed) function. Of course, the return value is not necessarily assigned to a reference variable name.

The Range property of an object such as Application,worksheet can return a Range object. A form of the Range property is:
The argument passed by Range (string arg) is a string.

For example,worksheets ("Sheet1"). Range ("A5") represents the A5 cell on the "Sheet1" form.
The expression of string arguments is flexible, except that the example above represents a single cell, and can represent a contiguous range of cells.
For example,worksheets ("Sheet1"). Range ("A1:b5") represents a contiguous range of cells A1 to B5 on the "Sheet1" form.

In addition, string parameters can represent a discontinuous range of cells to be grouped together.
For example,worksheets ("Sheet1"). Range ("A1:a10,c1:c10,e1:e10") represents a collection of three cell ranges on the Sheet1 form A1 to A10,c1 to C10,e1 to E10.

Note: When you use a string to represent the address of a cell or range of cells, you use the A1 notation, and you cannot use the R1C1 notation. For a few address representations of Excel cells or cell ranges, take a look at one of my other articles.

The string parameter of the Range property can not only represent the address of a cell or range of cells, but if you define the name of a cell or range of cells in the form, we can also access the cells on the form with defined names.

For example, we select a continuous cell on the "Sheet1" form, A1 to B5, and enter sample in "Name Box", which gives the A1:B5 this range a name of "sample". At this point, use worksheets ("Sheet1"). Range ("Sample") represents a cell or range followed by the use of worksheets ("Sheet1"). Range ("A1:b5") is equivalent.

The Range property is also a member of the class, so we can omit its restriction and write it as:

Range ("A1:b5") ,range ("Sample" ),range ("A5") , and so on.

When you do not add an object constraint, the Range property returns the cell or range on the active form. If the active form is not a worksheet (Worksheet), such as a chart Sheet, the statement will go wrong. Adding an object limiter is a good programming habit.

Another form of the Range property is:
Range (range Cell1, Rang Cell2) cell1 and cell2 two parameters are Range objects that represent a single contiguous cell at the diagonal Cell, which is the upper-left corner and the lower-right corner of the two cells.

For example, Range ("A1"), Range ("C5" ) represents the range of cells for a1:c5, which is equivalent to range ("A1:c5") . Therefore, we do not usually use the range attribute as nested, and the following cells attribute is often used in conjunction with this implementation of range.

The Cells property of an object such as Application,worksheet,range returns a Range object, but the Cells property returns a single cell. The Excel library does not have the cell class, which means that a single cell and a range of cells are in the range, and it depends on how big we initialize it. The syntax for the Cells property is:

Cells (row, column) row indicates that the cell is in the first row, andcolumn indicates that the cell is in the first column.

Worksheets (1). Cells (1, 1). Value = --This line of code sets the value of cell A1 to 24

The Cells property is also a member of the class, and we can omit its restriction, which defaults to the cells on the active form.

Cells (2, 1). Formula = "=sum (b1:b5)"   --Sets the formula for A2 cells on the active form

Range ("A1") and cells (all) represent cell A1, but the Cells property has a programmatic advantage, and its row and column parameters can be variables. Although we can pass arguments to the Range property through a string connection, it is a bit of a hassle.

The Range property, combined with the Cells property, is a range (cells (1, 1), Cells (5, 3)), which represents a contiguous range of cells a1:c5, which is equivalent to range ("A1:c5") . Let's look at an example:

With Worksheets(1)
    .Range(.Cells(1, 1), .Cells(10, 10)).Borders.LineStyle = xlThick
End With

Note the period before each cells property, at which point the returned cell is the cell on the first worksheet. If you remove a period, the cells on the active form are returned. (The first sheet is not necessarily an active form)

Assuming that rowindex and Columindex are two variables for example thread, the following section of code is based on the number of rows and columns entered by the user, from the A1 cell to the cell at the intersection of RowIndex and Columindex, starting with 1, and filling in the number by 1 increments per row.

rowindex = Val(InputBox("Please enter row number.", , 1))
columnindex = Val(InputBox("Please enter column number.", , 1))
For i = 1 To rowindex
    For j = 1 To columnindex
        Cells(i, j) = (i - 1) * columnindex + j
    Next j
Next i

When the Cells property is used with a Range object, the reference frame for the number of rows and columns is a Range object, that is, the rows and columns are relative to the upper-left cell of the Range object.

For exampleWorksheets(1).Range("C5:C10").Cells(2, 1) 指的是第一张表单上的C6 单元格。

Range 对象的 Offset 属性返回当前 Range 对象偏移指定的行数和列数后的一个Range 对象，大小不变，只是位置发生变化。语法：

Offset ( row, column) row and column are the offsets of rows and columns

Application and class method members Union (range1, Range2, ...) can combine multiple cell ranges as a Range object.

How to access values and manipulate cells in a cell with VBA code in Excel 2003-Columns of Tang and song ci-Blog Channel-csdn.net