Microsoft Office Tutorials and References

In Depth Information

**19.3.27 Formula and FormulaR1C1 Properties**

Dim sFirstHit As String ' Address of first hit

With Rows(1)

Set c = .Find(0, LookIn:=xlValues)

If Not c Is Nothing Then

sFirstHit = c.Address

Do

' Change cell contents

c.Value = ""

' find next cell

Set c = .FindNext(c)

Loop While Not c Is Nothing

End If

End With

The FindPrevious method has the syntax:

RangeObject
.FindPrevious(
Before
)

and works just like the FindNext method, but searches backward in the range starting with the cell

before
the cell referred to by the
Before
parameter (with wrap around from the beginning of the

range to the end).

19.3.27 Formula and FormulaR1C1 Properties

The Formula property returns or sets the formula or value for each cell in the range. The formula

must be expressed in A1-style notation, and must include a leading equal sign.

For instance, the line:

Range("A1").Formula = "=Sum(A2:A3)"

sets the formula in cell A1. The line:

Range("A1:C1").Formula = "=Sum(A2:A3)"

places the formula in cells A1:C1, but because the formula uses relative references, these

references will be altered as usual. If we want to put the exact same formula in each cell, we must

use an array, as in:

Range("A1:C1").Formula = _

Array("=Sum(A2:A3)", "=Sum(A2:A3)", "=Sum(A2:A3)")

We can also return an array using the Formula property. To illustrate, consider the worksheet in

Figure 19-13
.
The code:

Dim a As Variant

a = Range("A1:C2").Formula

sets the Variant variable
a
to an array, so that, for instance,
a(2,3)
=
7
. Note that the Formula

property returns a Variant, so that
a
must be declared as a Variant.

Figure 19-13. Illustrating the Formula property