Combining R1C1 and absolute reference in SUMPRODUCT in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
To set an absolute reference, omit the [ ] part and enter the Row ,Column
number direct.

e.g. R10C4:R20C6

Don
 
Back
Top