Combining R1C1 and absolute reference in SUMPRODUCT in VBA
 

I am having trouble figuring out how to write a SUMPRODUCT formula in VBA
that multiples an array of cells which I would like to reference in relative
R1C1 terms by an array of cells that I would like to reference in absolute
terms on another sheet. The first part of my formula is fine, but I want the
cells from the DI_NAM sheet to always refer to rows 5 to 9. The column must
be the same as the first array. If there is a way I could put a variable
into the reference, that would work, but I can't figure out how to do this.
I need to replicate this formula as part of a block of data that gets created
in a do loop.

Selection.FormulaR1C1 = "=SUMPRODUCT(R[-7]C:R[-3]C,DI_NAM!R[-17]C:R[-13]C)"

This formula gets input across 36 columns as I am working with 36 months of
data.

I am a novice at VBA, so if anyone knows how to do this, I would really
appreciate the help!

Combining R1C1 and absolute reference in SUMPRODUCT in VBA
 

To set an absolute reference, omit the [ ] part and enter the Row ,Column
number direct.

e.g. R10C4:R20C6

Don

"Cornelia" wrote in message
...
I am having trouble figuring out how to write a SUMPRODUCT formula in VBA
that multiples an array of cells which I would like to reference in
relative
R1C1 terms by an array of cells that I would like to reference in absolute
terms on another sheet. The first part of my formula is fine, but I want
the
cells from the DI_NAM sheet to always refer to rows 5 to 9. The column
must
be the same as the first array. If there is a way I could put a variable
into the reference, that would work, but I can't figure out how to do
this.
I need to replicate this formula as part of a block of data that gets
created
in a do loop.

Selection.FormulaR1C1 =
"=SUMPRODUCT(R[-7]C:R[-3]C,DI_NAM!R[-17]C:R[-13]C)"

This formula gets input across 36 columns as I am working with 36 months
of
data.

I am a novice at VBA, so if anyone knows how to do this, I would really
appreciate the help!