How to find the product of a range of cell in excel using VBA?

Question

I would like to get the product of values of a range of cells in excel, using VBA.

I got a selection e.g.: (that works)

ActiveSheet.Range(Cells(i, j), Cells(i, z)).Select

I would like to put the product of a range in another cell but this is not working:

'Range("X10").Formula = "=PRODUCT(Range(Cells(i, j),Cells(i, z)))"

Help is really appreciated, thanks in advance!

You'd need an worksheet reference in the product formula. Try (Range(Cells(i,j),Cells(i,)).Address & ":" & Range(Cells(i,j),Cells(i,z)).Address. (Not doing this with Excel open - hope I've got the parentheses right) — chuff

Abe Gold Abe Gold · Accepted Answer · 2013-06-19T19:36:49

Range("X10").Formula = "=PRODUCT(" & Cells(i, j).Address & ":" & Cells(i, z).Address & ")"