Using SumProduct function in VBA

[Colin Macleod]

I'd appreciate some help in using the sumproduct function. I have a set of
numbers in cells a1 through to j1, then a table of numbers starting in cell
a3 and going across and down to cell j100. In cell k3, I need to put the
function =SUMPRODUCT(A$1:J$1,A3:J3) then copy this down the column. I'd like
to put it into a for/next loop as the start and end row will vary. I'd also
like column k to show values rather than contain formulas.

Thanks for any help.

Colin

 

[michdenis]

Hi,

Try this :

'---------------------------------------
Sub Formula()
Dim LastRow As Long
With Worksheets("Sheet1") 'Adapt sheet name
lastrow = .Range("A:J").Find(What:="*", _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
With .Range("K3:K" & lastrow)
.Formula = "=SUMPRODUCT(" & .Parent.Name & _
"!A$1:J$1," & .Parent.Name & _
"!A3:J" & .Row & ")"
.Value = .Value
End With

End With
End Sub
'---------------------------------------



"Colin Macleod" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
I'd appreciate some help in using the sumproduct function. I have a set of
numbers in cells a1 through to j1, then a table of numbers starting in cell
a3 and going across and down to cell j100. In cell k3, I need to put the
function =SUMPRODUCT(A$1:J$1,A3:J3) then copy this down the column. I'd like
to put it into a for/next loop as the start and end row will vary. I'd also
like column k to show values rather than contain formulas.

Thanks for any help.

Colin