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
' 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
Search JabSto ::

Custom Search