I am aware of the basic usage of relative references in establishing a
formula. For example to sum a column of cells relative to a given cell the
following can be used:
ActiveCell.FormulaR1C1="=sum(R[-5]C[-2]:R[-1]C[-2])"
However, since the formula is a string, it is not possible to replace, for
instance, -5 with a variable. Is there a straightforward way to do this?

Dim myVar as double 'your variable

MyVar = -5

ActiveCell.FormulaR1C1="=sum(R[" & myVar & "]C[-2]:R[-1]C[-2])"

"VegasPines" wrote:

I am aware of the basic usage of relative references in establishing a
formula. For example to sum a column of cells relative to a given cell the
following can be used:
ActiveCell.FormulaR1C1="=sum(R[-5]C[-2]:R[-1]C[-2])"
However, since the formula is a string, it is not possible to replace, for
instance, -5 with a variable. Is there a straightforward way to do this?

ker_01,

Thanks very much. That works just fine. It's great to know there is this
kind of help available since getting the same kind of help from the Microsoft
help function seems to be impossible. Either I don't know how to ask the
question or it is really that difficult.

VegasPines
--
VegasPines


"ker_01" wrote:


Dim myVar as double 'your variable

MyVar = -5

ActiveCell.FormulaR1C1="=sum(R[" & myVar & "]C[-2]:R[-1]C[-2])"

"VegasPines" wrote:

I am aware of the basic usage of relative references in establishing a
formula. For example to sum a column of cells relative to a given cell the
following can be used:
ActiveCell.FormulaR1C1="=sum(R[-5]C[-2]:R[-1]C[-2])"
However, since the formula is a string, it is not possible to replace, for
instance, -5 with a variable. Is there a straightforward way to do this?

Conceptually compared to a database language programming the relativity thing
takes a bit of getting used to. And given the very nature of spreadsheets and
that you can add or delete Rows or Columns in an instance that means that
anything to do with range definItions in VBA can take some effort. It
therefore amazes me that so many of the examples in the Help files only use
"A1:D5" style notation rather than more useful relational referencing.

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"VegasPines" wrote:

ker_01,

Thanks very much. That works just fine. It's great to know there is this
kind of help available since getting the same kind of help from the Microsoft
help function seems to be impossible. Either I don't know how to ask the
question or it is really that difficult.

VegasPines
--
VegasPines


"ker_01" wrote:


Dim myVar as double 'your variable

MyVar = -5

ActiveCell.FormulaR1C1="=sum(R[" & myVar & "]C[-2]:R[-1]C[-2])"

"VegasPines" wrote:

I am aware of the basic usage of relative references in establishing a
formula. For example to sum a column of cells relative to a given cell the
following can be used:
ActiveCell.FormulaR1C1="=sum(R[-5]C[-2]:R[-1]C[-2])"
However, since the formula is a string, it is not possible to replace, for
instance, -5 with a variable. Is there a straightforward way to do this?