PDA

View Full Version : Changing variables in SumProduct formula



Billy C
01-28-2014, 08:29 AM
I have the following line of code that works for a critera set in sheet Target cell A" and returns in sheet Target cell B2 the correct answer, however when it moves down a row it still refers to A2 not A3can anyone advise me how to progress the A2 to become A3 and so onI have tried storing an intiger in a variable R and changing the code to =Target!A" & R, but i get an errorCells(R, 2).Formula = "=SUMPRODUCT((Data!A2:A" & DataLastRow & " =Target!A2)*Data!B2:B" & DataLastRow & ")"

lecxe
01-28-2014, 11:57 AM
Hi Billy

Not sure I understood correctly, but I guess you want something like:



Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!$A$2:$A$" & DataLastRow & " =Target!A2)*Data!$B$2:$B$" & DataLastRow & ")"


Please try.

Billy C
01-28-2014, 12:26 PM
Hi lecxe
the code is part of a larger piece, what I am trying to do is as I step through moving down a cell after doing some checks it will perform a sum product calculation if I am in cell B5 I want the =Target!A2 to become =Target!A5

Is this something you can help with please?
it all works except this section

Bob Phillips
01-29-2014, 02:10 AM
Do it in a single statement as lecxe shows but don't make it absolute


Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!$A2:$A" & DataLastRow & " =Target!A2)*Data!$B2:$B" & DataLastRow & ")"

Billy C
01-29-2014, 02:34 AM
That works fantastic, thank you very much indeed.Not much difference in the code but a small tweak makes such a difference

lecxe
01-29-2014, 02:41 AM
Hi Billy

Check xld answer, I may have misunderstood what you need and his answer may be what you're looking for.


... what I am trying to do is as I step through moving down a cell after doing some checks it will perform a sum product calculation if I am in cell B5 I want the =Target!A2 to become =Target!A5


The way I interpret is is that you want the SumProduct() formula to be written in a cell in Target!B:B and the formula should refer to the cell in the same row in Target!A:A.

The references in worksheet Data, columns A and B are some kind of lookup list and will not change.

1 - Writing the formulas to a group of cells

If this is true, then the statement I posted will write the formula in a group of cells in Target!B:B, each formula referring to the cell to the left in the same row.

Open a new workbook, rename 2 of the sheets to "Data" and "Target" and execute:



Sub test()
Dim DataLastRow As Long

DataLastRow = 10

Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!$A$2:$A$" & DataLastRow & " =Target!A2)*Data!$B$2:$B$" & DataLastRow & ")"

End Sub


Check the formulas written in Target!B2:B5

2 - Writing 1 formula to 1 cell

If you prefer to write just 1 formula at a time, then an easy way is to use the R1C1 notation and to refer to "RC[-1]" which means a cell in the same row but 1 column to the left. This means that if you write the formula for ex. in Target!B8 then the formula refers to Target!A8

Try:



Sub test()
Dim DataLastRow As Long

DataLastRow = 10

Worksheets("Target").Range("B8").FormulaR1C1 = _
"=SUMPRODUCT((Data!R2C1:R" & DataLastRow & "C1 =Target!RC[-1])*Data!R2C2:R" & DataLastRow & "C2)"

End Sub


I hope that you now have enough information to solve your problem.
If not, post back.

lecxe
01-29-2014, 02:43 AM
Sorry, can't find the command to delete my previous post.

I see that xld's solution solved your problem so disregard my last post.

I'm glad your problem is solved. Cheers!

Billy C
01-29-2014, 02:54 AM
Thank youYour help has been much appreciated

Bob Phillips
01-29-2014, 03:12 AM
Glad it is solved, but a couple oif questions.

1) Should I have made last row absolute


Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!$A2:$A$" & DataLastRow & " =Target!A2)*Data!$B2:$B$" & DataLastRow & ")"

2) Is SUMPRODUCT necessary here, couldn't you have used SUMIF?


Worksheets("Target").Range("B2:B5").Formula = _
"=SUMIF(Data!$A2:$A" & DataLastRow & " ,Target!A2,Data!$B2:$B" & DataLastRow & ")"

Billy C
01-29-2014, 03:33 AM
Thank you