sumproduct or other way in vba, help and thanks

[Guest]

dear all

a question about sumproduct function, in a sheet
A B C
1) 2 0 1
2) 4 5 6
3) 7 8 9
4) 10 11 12

Now I can use 2 sumproduct to make calculation,like
A1*C1+A2*C2 in one sumproduct
A4*C4 in another sumproduct
then I add result together.

My question is, is there anyway to make a formula
A1*C1+A2*C2+A4*C4. I could choose different array then define in more detail
because sumproduct ask all array should have same dimension.

Thanks in advance.

 

[Bob Phillips]

You can just use SUM

=SUM(A1*C1,A2*C2,A4*C4)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, Bob

But it couldn't really solve my problem because I'm trying to make input
data easier, what I really want to know is whether a function could let user
select arrays and get calculation directly, instead of having to type formula.

My example is too simple, the real situation is that user might have to make
very long formula, and sometimes it's easy to make mistakes. It's much easier
if people just select array and ger result.

Anyway, thanks again for your prompt reply.
jiang

 

[Bob Phillips]

This might work for you

Sub SumResult()
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Dim cRowOff As Long
Dim cColOff As Long
Dim tmp

Set rng1 = Application.InputBox("Select all cells in first range",
Type:=8)
Set rng2 = Application.InputBox("Select just the first cell in second
range", Type:=8)
cRowOff = rng2.Cells(1, 1).Row - rng1.Cells(1, 1).Row
cColOff = rng2.Cells(1, 1).Column - rng1.Cells(1, 1).Column
For Each cell In rng1
tmp = tmp + (cell.Value * cell.Offset(cRowOff, cColOff).Value)
Next cell

Set cell = Application.InputBox("Now select cell to put result",
Type:=8)
cell.Value = tmp

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)