about Cells and R1C1

[xq]

i just couldn't solve it, i was tying to put formula from column D to column O, i used for loop, but i also used RC notaion in the SUMPRODUCT, but it didn't work properly, and i don't know what's wrong with that, or i shouldn't write as :
Range(Cells(3, i), Cells(15, i)).FormulaArray ?

For i = 4 To 15
Worksheets("TotValume&quot.Activate
Range(Cells(3, i), Cells(15, i)).FormulaArray = _
"=SUMPRODUCT(....)"
Next i

please, help me out! thanks a lot!

 

[xlbo]

Sumproduct isn't an array formula therefore you shouldn't use formulaarray. R1C1 is relative referencing hence (probably) the reason this didn't work. Just use .formula

Range(Cells(3, i), Cells(15, i)).Formula ="=SUMPRODUCT(....)"

HTH
Geoff

 

[xq]

thanks a lot, i just tried, RC is working now, but the formula returns #VALUE, the result is based on multiple creteriam, i have to use formula array, actually i don't think there should be anything wrong with the formula, cos that formula was working on some other wroksheet (that is not written by vba code), i just rewrite it in the vba code and doesn't work, i just don't get it!

 

[xlbo]

From excel help:

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ... are 2 to 30 arrays whose components you want to multiply and then add.

· The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

Have you checked the dimensions of your array arguments - this would seem to indicate the possible source of the #VALUE! error
HTH
Geoff