VBA Sumproduct

[helpexcel]

Hi,

I was going to write code that put this formula in Columns D to the last Column.

=SUMPRODUCT((Sheet1!$A$3:$A$1000=Sheet2!$A3)+0,(Sheet1!$B$3:$B$1000=Sheet2!A$2)+0,(Sheet1!$C$3:$C$1000)+0)

How do I make this portion dynamic? Sheet1!$A$3:$A$1314
Is there code i can use instead of putting this formula in each row?

 

[JackDanIce]

Does this help?

Sub M1()

Dim LR      As Long
Dim x       As Long
Dim sFunc   As String

'Some arbitary random value for last row, minimum 5
LR = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, 5)

sFunc = "=SUMPRODUCT((Sheet1!$A$3:$A$@LR=Sheet2!$A3)+0,(Sheet1!$B$3:$B$@LR=Sheet2!A$2)+0,(Sheet1!$C$3:$C$@LR)+0)"

For x = 1 To 5
    Cells(x, 1).Formula = Replace(sFunc, "@LR", LR)
Next x

End Sub

 

[helpexcel]

It gets the formula right, but it puts the formula in A1 and all the way down depending on x. I need it to start in D and goto to last column/last row.

 

[JackDanIce]

Try:

Sub M1()

    Dim LR      As Long
    Dim x       As Long
    Dim sFunc   As String

    'Last row in column D (4)
    LR = Cells(Rows.Count, 4).End(xlUp).Row
    
    sFunc = "=SUMPRODUCT((Sheet1!$A$3:$A$@LR=Sheet2!$A3)+0,(Sheet1!$B$3:$B$@LR=Sheet2!A$2)+0,(Sheet1!$C$3:$C$@LR)+0)"
    
    For x = 1 To LR
        Cells(x, 4).Formula = Replace(sFunc, "@LR", LR)
    Next x

End Sub

Change 1 to whatever row the data starts on

 

[helpexcel]

How would i adjust this to fill to lastrow and last column, starting M3?

As i'm reading this, think i can just use:

.Range(.Cells(3, 13), .Cells(LR, LastColumn)).Formula = Replace(sFunc, "@LR", LR)

 

[JackDanIce]

Try it, the code looks right to cover a range

 

[helpexcel]

yep works! Do you know if there is a way to do away with the formula altogether, to speed things up?

 

[JackDanIce]

Probably is but I don't know contents of your sheet, hence what the formula is calculating. Looking purely at the formula, it may be possible to speed up macro by using arrays and dictionaries, or using EVALUATE within the VBA code to output values, but I can't use EVALUATE well enough to suggest.

 

[helpexcel]

I have a large worksheet of data on Sheet1 that i am trying to consolidate. I am using SumProduct to look at the columns A,B,C and then place the "price" in the appropriate column on Sheet2.

From this:

Color	Shape	Code	From	Price
Blue	Square	12345	Comp A	$100.00
Blue	Circle	9876	Comp A	$100.00
Blue	Square	12345	Comp B	$50.00
Blue	Square	12345	Camp C	$300.00
To this:
Color	Shape	Code	Comp A	Comp B	Comp C
Blue	Square	12345	$100.00	$50.00	$300.00
Blue	Circle	9876	$100.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[JackDanIce]

A pivot table can do this for you, why not just use one of those?