Consulting

Results 1 to 10 of 10

Thread: Changing variables in SumProduct formula

  1. #1

    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 =Target!A" & R, but i get an errorCells(R, 2).Formula = "=SUMPRODUCT((Data!A2:A" & DataLastRow & " =Target!A2)*Data!B2:B" & DataLastRow & ")"

  2. #2
    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.

  3. #3
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    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 & ")"
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

  6. #6
    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 View Post
    ... 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.

  7. #7
    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!

  8. #8
    Thank youYour help has been much appreciated

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    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 & ")"
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •