+ Reply to Thread
Results 1 to 6 of 6

Sumproduct With loop

  1. #1
    Registered User
    Join Date
    07-09-2020
    Location
    South Africa
    MS-Off Ver
    MS 2017
    Posts
    4

    Sumproduct With loop

    Hello everyone,

    I'm trying to create a loop with a sumproduct function.
    I can use the classic sumproduct function without VBA but my file can contain thousands of lines.
    in the attached file, sheet "result expected" I present the results i wish to obtain.

    Thanks a lot for your help,
    Kind regards,
    Attached Files Attached Files
    Last edited by nba_abc; 07-10-2020 at 07:20 PM.

  2. #2
    Registered User
    Join Date
    07-09-2020
    Location
    South Africa
    MS-Off Ver
    MS 2017
    Posts
    4

    Re: Sumproduct With loop

    Hello everyone,

    Does anyone have any ideas on this?

    Thanks a lot,
    Kind regards

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,464

    Re: Sumproduct With loop

    Please Login or Register  to view this content.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    07-09-2020
    Location
    South Africa
    MS-Off Ver
    MS 2017
    Posts
    4

    Re: Sumproduct With loop

    Hello protonLeah,

    Thanks a lot for your help! This is what I've been looking for!

    However, when I extend the table into sheet "products" and "stock" to reach line 10, I have this message: "Run time error 9: “Subscript out of Range”
    Do you have a solution?

    Thank you,
    Kind regards

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,464

    Re: Sumproduct With loop

    Small change. The result array needs # columns = # rows in prod and stock sheets, so:

    ReDim resultarray(1 To UBound(ProductArray, 1), 1 To UBound(ProductArray, 1))

  6. #6
    Registered User
    Join Date
    07-09-2020
    Location
    South Africa
    MS-Off Ver
    MS 2017
    Posts
    4

    Re: Sumproduct With loop

    Hello protonLeah,

    Thanks a lot, this is perfect!

    Kind regards
    Last edited by nba_abc; 07-15-2020 at 05:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combine sumif/sumproduct or sumproduct with multiple criteria
    By sab128 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2018, 08:25 AM
  2. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  3. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  4. SUMPRODUCT Loop Question
    By dash11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2012, 05:33 PM
  5. Sumproduct, three formulas ,or if loop?
    By Cyberpawz in forum Excel General
    Replies: 4
    Last Post: 03-21-2012, 09:15 AM
  6. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  7. Large SumProduct Array -- original title:Loop Function
    By Divius in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-21-2007, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1