Changing variables in SumProduct formula

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

Code:

---

=Target!A" & R

---

, but i get an errorCells(R, 2).Formula =

Code:

---

"=SUMPRODUCT((Data!A2:A" & DataLastRow & " =Target!A2)*Data!B2:B" & DataLastRow & ")"

---

Hi Billy

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

Code:

---

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

---

Please try.

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

Do it in a single statement as lecxe shows but don't make it absolute

Code:

---

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

---

That works fantastic, thank you very much indeed.Not much difference in the code but a small tweak makes such a difference

Hi Billy

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

Quote:

---

Originally Posted by Billy C

... 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:

Code:

---

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:

Code:

---

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.

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!

Thank youYour help has been much appreciated

Glad it is solved, but a couple oif questions.

1) Should I have made last row absolute

Code:

---

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?

Code:

---

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

---

Thank you