Microsoft Office Tutorials and References
In Depth Information
19.3.4 AutoFill Method
The Address property returns the range reference of the Range object as a string. The syntax is:
RangeObject .Address( RowAbsolute , ColumnAbsolute , _
ReferenceStyle , External , RelativeTo )
RowAbsolute is set to True (the default) to return the row part of the reference as an absolute
reference. ColumnAbsolute is set to True (the default) to return the column part of the
reference as an absolute reference.
ReferenceStyle can be one of the XlReferenceStyle constants xlA1 or xlR1C1 . The
default value is xlA1 .
Set the External parameter to True to return an external reference—that is, a reference that is
qualified by the workbook and worksheet names and is thus valid outside the current worksheet.
The default value of False returns a reference that is not qualified and is therefore valid only
within the current worksheet.
Finally, the RelativeTo parameter is used when RowAbsolute and ColumnAbsolute are
False and ReferenceStyle is xlR1C1 . In this case, we must include a reference point (a cell)
to use for the relative addresses. Let us consider some examples to help clarify this property:
Set rng = Range("B2")
rng.Address(ReferenceStyle:=xlA1) ' Returns $B$2
rng.Address(ReferenceStyle:=xlA1, _
External:=True) ' Returns
[Book1]Sheet1!$B$2
rng.Address(ReferenceStyle:=xlR1C1) ' Returns R2C2
rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False, ReferenceStyle:=xlA1) ' Returns B2
rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False, ReferenceStyle:=xlR1C1, _
RelativeTo:=Range("D1")) ' Returns R[1]C[-2]
Set rng = Range("B2:D5")
rng.Address(ReferenceStyle:=xlA1) ' Returns $B$2:$D$5
rng.Address(ReferenceStyle:=xlR1C1) ' Returns R2C2:R5C4
rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False, ReferenceStyle:=xlA1) ' Returns B2:D5
rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False, ReferenceStyle:=xlR1C1, _
RelativeTo:=Range("D1")) ' Returns R[1]C[-
2]:R[4]C
19.3.4 AutoFill Method
This important method performs an autofill on the cells in the range. Its syntax is:
RangeObject .AutoFill( Destination , Type )
Here Destination is the Range object whose cells are to be filled. The destination must include
the source range—that is, the range that contains the data to use for the autofill.
The optional Type parameter specifies the fill type. It can be one of the following
XlAutoFillType constants (note that the Excel documentation refers to a nonexistent
XlFillType enum):
Search JabSto ::




Custom Search