View Full Version : [SOLVED:] 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.