Changing variables in SumProduct formula

[Billy C]

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]

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]

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]

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]

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

[lecxe]

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]

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]

Thank youYour help has been much appreciated

[Bob Phillips]

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]

Thank you