VBA to sumproduct selected cells

[ferion132]

Hi guys,

　
Would like to ask for some help on a simple VBA formula that would allow me to sumproduct my cells selected and to give the result in the cell below.

　
e.g.
I would highlight cells A1 to B3, and the VBA would sumproduct the selected cells (A1 to A3) and (B1 to B3) and give the result in the cell below at B4.
　
Reason why I need it to calculate for selected cells is because the data range constantly changes.
　
Thanks for your help!

 

[JackDanIce]

Welcome to the board.

You can account for varying rows by determining the last used row. See if this code helps:

Dim LR as Long
Const mySUMPRODUCT as String = "=SUMPRODUCT(A1:A@LR,B1:B@LR)"

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Formula = Replace(mySUMPRODUCT, "@LR", LR)

 

[ferion132]

Welcome to the board.

You can account for varying rows by determining the last used row. See if this code helps:

Dim LR as Long
Const mySUMPRODUCT as String = "=SUMPRODUCT(A1:A@LR,B1:B@LR)"

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Formula = Replace(mySUMPRODUCT, "@LR", LR)

Hi thanks for your reply, I am new to VBA, when I run the code it says compile error, constant expression required

 

[JackDanIce]

My error, try:

Dim LR as Long
Dim str as String

str = “=SUMPRODUCT(A1:A@LR,B1:B@LR)”
LR = Cell(Rows.Count, 1).End(xlUp).Row

Cells(LR + 1, 1).Formula = Replace(str, “@LR”, LR)