SUMPRODUCT in macro
 

Here is what i have

buttonClick()
'ommited defined variables and other useless stuff

CurrentSheet.Cells(CurrentRow, 6).Formula = "=SUMPRODUCT(B2:E2{1,3,2,3})"

What i would like is the range in the formula to increase as the Current row
increases so =SUMPRODUCT(B3:E3{1,3,2,3}) and so on down the table

In Short the 6th column of every row needs a SUMPRODUCT function for the 3rd
through 5th coulmn

i think i have to define a variable but not sure here.

Somthing like =SUMPRODUCT(B(i):E(i){1,3,2,3}) maybe?
any help would be great.

SUMPRODUCT in macro
 

i = 5
"=SUMPRODUCT(B" & i & ":E" & i & "{1,3,2,3})"

"Office_Novice" wrote:

Here is what i have

buttonClick()
'ommited defined variables and other useless stuff

CurrentSheet.Cells(CurrentRow, 6).Formula = "=SUMPRODUCT(B2:E2{1,3,2,3})"

What i would like is the range in the formula to increase as the Current row
increases so =SUMPRODUCT(B3:E3{1,3,2,3}) and so on down the table

In Short the 6th column of every row needs a SUMPRODUCT function for the 3rd
through 5th coulmn

i think i have to define a variable but not sure here.

Somthing like =SUMPRODUCT(B(i):E(i){1,3,2,3}) maybe?
any help would be great.

SUMPRODUCT in macro
 

if i = 5

it doesn't do what i need, I want the variable to vary if i assign it a
value i wont cahne with the current row.

"Joel" wrote:

i = 5
"=SUMPRODUCT(B" & i & ":E" & i & "{1,3,2,3})"

"Office_Novice" wrote:

Here is what i have

buttonClick()
'ommited defined variables and other useless stuff

CurrentSheet.Cells(CurrentRow, 6).Formula = "=SUMPRODUCT(B2:E2{1,3,2,3})"

What i would like is the range in the formula to increase as the Current row
increases so =SUMPRODUCT(B3:E3{1,3,2,3}) and so on down the table

In Short the 6th column of every row needs a SUMPRODUCT function for the 3rd
through 5th coulmn

i think i have to define a variable but not sure here.

Somthing like =SUMPRODUCT(B(i):E(i){1,3,2,3}) maybe?
any help would be great.

SUMPRODUCT in macro
 

There are two methods

For RowCount = 1 to 10
Range("A" & Rowcount).formula = _
"=SUMPRODUCT(B" & RowCount & ":E" & Rowcount & "{1,3,2,3})"
Next RowCount

or put the formula in one cell and copy the cell
Range("A1").formula = _
"=SUMPRODUCT(B" & RowCount & ":E" & Rowcount & "{1,3,2,3})"
Range("A1").copy destination:=Range("A2:A10")

copying a cell with a formula in VBA is like copying the formula on the
worksheet, the rows will change.


"Office_Novice" wrote:

if i = 5

it doesn't do what i need, I want the variable to vary if i assign it a
value i wont cahne with the current row.

"Joel" wrote:

i = 5
"=SUMPRODUCT(B" & i & ":E" & i & "{1,3,2,3})"

"Office_Novice" wrote:

Here is what i have

buttonClick()
'ommited defined variables and other useless stuff

CurrentSheet.Cells(CurrentRow, 6).Formula = "=SUMPRODUCT(B2:E2{1,3,2,3})"

What i would like is the range in the formula to increase as the Current row
increases so =SUMPRODUCT(B3:E3{1,3,2,3}) and so on down the table

In Short the 6th column of every row needs a SUMPRODUCT function for the 3rd
through 5th coulmn

i think i have to define a variable but not sure here.

Somthing like =SUMPRODUCT(B(i):E(i){1,3,2,3}) maybe?
any help would be great.