Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    SUMPRODUCT to VBA

    Hi Guys,

    I am trying to move this cell function into the VBA with not much luck:

    Code:
    =SUMPRODUCT(C3:C47,M3:M47)
    In case anyone is wondering, I change values on the sheet with the VBA so I want to update the result of this SUMPRODUCT at the same time.
    Thanks
    Ferenc

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Can you show the line of code in context so we have some idea of what we're working with? Also please show what you have already tried. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    try

    Code:
    x=Worksheetfunction.SUMPRODUCT(Range("C3:C47"),Range("M3:M47"))
    Maud

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Ferenc Nagy (2015-07-22)

  5. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ferenc,

    Can you show the line of code in context so we have some idea of what we're working with? Also please show what you have already tried. HTH
    I haven't tried anything because I did not know how. That line currently sits in a cell on one of my sheets. I am converting the sheet to be fully automated so whenever I update something from another sheet, I can just call the subroutine to recalculate the item and then this, the sum product... Column C is the Quantity, Column M is the Price.

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I don't understand why you need to replace the formula? It will recalculate if you change the input values with VBA (assuming you didn't turn calculation to manual).
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    I don't understand why you need to replace the formula? It will recalculate if you change the input values with VBA (assuming you didn't turn calculation to manual).
    To remove the possibility of an accidental cell content deletion...

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Ferenc Nagy View Post
    To remove the possibility of an accidental cell content deletion...
    Ferenc,

    You can use the Sheet/Workbook protection features to accomplish that task very easily. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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