Multiply 2 columns and then sum the totals VBA

[aheverett1]

Hello,

Is there a way to take multiply say A1 * B1, A2 * B2, A3 * B3 then put the extended totals add them together and add those up?

Like this but without using a third column and just putting the total of 196 in a cell?

[TABLE="width: 192"]
<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>[TR]
[TD="class: xl205, width: 64"]QTY[/TD]
[TD="class: xl205, width: 64"]Price[/TD]
[TD="class: xl205, width: 64"]Ext price[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl205"]Total
[/TD]
[TD="align: right"]196
[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

try this:

Sub NoHelper()
    Dim i As Long, lr As Long
    Dim x As Double, y As Double
    lr = Range("A" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 2 To lr
        y = Range("A" & i) * Range("B" & i)
        x = x + y
    Next i
    Range("A" & lr + 1) = "total"
    Range("B" & lr + 1) = x




End Sub

 

[Dave Patton]

try the following

Excel 2010
	A	B
1	QTY	Price
2	1	66
3	1	85
4	3	15
5	Total	196
6a

Cell Formulas
Range	Formula
B5	=SUMPRODUCT(A2:A4,B2:B4)

 

[aheverett1]

OK, should have been a little more specific. My first range actually starts B6 and the other range starts at I6

Thought this would fix it, but it did not.

Private Sub CommandButton1_Click()
Dim i As Long, lr As Long
Dim x As Double, y As Double
lr = Range("B6" & Rows.Count).End(xlUp).Row
x = 0
For i = 2 To lr
y = Range("B6" & i) * Range("I6" & i)
x = x + y
Next i
Range("B6" & lr + 1) = "total"
Range("I6" & lr + 1) = x
End Sub


Also, how would i output the total or the loop into cell "I1"?

 

[aheverett1]

That worked beautifully! That's a new formula to me.

 

[alansidman]

Amended Code to fit your better description:

Option Explicit
Sub Nohelper()
    Dim i As Long, lr As Long
    Dim x As Double, y As Double
    lr = Range("B" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 6 To lr
        y = Range("B" & i) * Range("I" & i)
        x = x + y
    Next i
    Range("I1") = x


End Sub

PLease use code tags when posting VBA