Im trying to write a formula into a cell, which will later be copied down its column but Im struggling with understanding how to write the formula in vba so it is suitable to be copied down.
The formula will be written to the right of a set of data which has a variable number of columns but always begins in column c and will total those columns which have TRUE in row 9.
If i were to write this in the excel gui fixed for two columns (C+D) the cell E8 would contain
=SUMPRODUCT($C$9:D$9*$C12:D12) which would copy down as expected
but in vba i want to be able to alter the column reference eg
If 'LC' holds the last column as a long and I wanted to write the formula on row 12 it would say the equivalent to
(12, LC + 1).Value = _
"=SUMPRODUCT($C$9:$(LAST_COLUMN)$9 * $C12:(LAST_COLUMN)12)"
How would I write this in VBA so the copying down would work?
Thanks
nic
The formula will be written to the right of a set of data which has a variable number of columns but always begins in column c and will total those columns which have TRUE in row 9.
If i were to write this in the excel gui fixed for two columns (C+D) the cell E8 would contain
=SUMPRODUCT($C$9:D$9*$C12:D12) which would copy down as expected
but in vba i want to be able to alter the column reference eg
If 'LC' holds the last column as a long and I wanted to write the formula on row 12 it would say the equivalent to
(12, LC + 1).Value = _
"=SUMPRODUCT($C$9:$(LAST_COLUMN)$9 * $C12:(LAST_COLUMN)12)"
How would I write this in VBA so the copying down would work?
Thanks
nic
