+ Reply to Thread
Results 1 to 6 of 6

Code expression for SUMPRODUCT

  1. 02-04-2015, 09:13 AM #1
    larrysdime
    larrysdime is offline
    Registered User
    Join Date
    12-19-2014
    Location
    Maryland
    MS-Off Ver
    Office 2010
    Posts
    40

    Code expression for SUMPRODUCT

    Good Morning Everyone,

    I am stuck on getting the code expression right on a SUMPRODUCT function in my code for a project and could use some help. Here is my code that I need help with:

    txtMultiplier.Value = Application.WorksheetFunction.SUMPRODUCT(--(txtCostEach>=Sheet4!(D5:D30),--(txtCostEach<=Sheet4!(E5:E30),Sheet4(F5:F30))

    I am looking up the correct multiplier in order to multiply by the cost to determine ultimately the sell price for the part. Basically, if the cost each value is equal to or greater than the values in column D5 thru D30 and less then or equal to the value in Column E5:E30 it produces the appropriate Multiplier From Column F5:F30.

    I have the formula calculating quite nicely on the worksheet, but can't seem to get the code expression right to make it functional in my code. Could anyone assist with the right expression?

    Here is an example file of what I am trying to do: SUMPRODUCT Function Find Multiplier Example.xlsm

    Thanks,
    Larry
    Last edited by larrysdime; 02-04-2015 at 09:54 AM.
    Reply With QuoteRegister To Reply
  2. 02-04-2015, 09:54 AM #2
    StuCram
    StuCram is offline
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: Code expression for SUMPRODUCT

    And good morning to you as well,

    I noticed 2 things…

    1. A "!" is missing after "Sheet4" in the last part of the formula.

    2. The parentheses are not balanced properly; each part srtaing with "--" needs a closing ")".

    Here's my suggested correction:
    Please Login or Register  to view this content.
    Another option to consider would be to name the 3 ranges on sheet4; perhaps, lowerLimits, upperLimits, and multipliers. Then your formula is easier (to me at least) and would not have to be changed when you extend the ranges.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-04-2015, 09:58 AM #3
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,239

    Re: Code expression for SUMPRODUCT

    You can't use arrays like that in VBA. However, all you need is Lookup:

    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head
    Reply With QuoteRegister To Reply
  4. 02-04-2015, 10:07 AM #4
    StuCram
    StuCram is offline
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: Code expression for SUMPRODUCT

    My mistake - I confused Excel formula with VBA.
    Reply With QuoteRegister To Reply
  5. 02-04-2015, 10:08 AM #5
    larrysdime
    larrysdime is offline
    Registered User
    Join Date
    12-19-2014
    Location
    Maryland
    MS-Off Ver
    Office 2010
    Posts
    40

    Re: Code expression for SUMPRODUCT

    Good Morning Neighbor,

    Thanks for reaching out. I just tried the first recommendation and am still getting a compile error. I like your idea of naming the High and Low Ranges etc. and may give this a try.

    For Now I have reattached the project with the code and user form on it and was hoping you could take a look at the compile error.

    Here is the updated file:SUMPRODUCT Function Find Multiplier Example.xlsm

    Thanks Again
    Reply With QuoteRegister To Reply
  6. 02-04-2015, 10:19 AM #6
    larrysdime
    larrysdime is offline
    Registered User
    Join Date
    12-19-2014
    Location
    Maryland
    MS-Off Ver
    Office 2010
    Posts
    40

    Re: Code expression for SUMPRODUCT

    Thank you all for your Help. Romperstomper, the code worked perfectly!!!

    I have reattached a copy of the project with the correct Code and User form in case other may want to use it as an example for future purposes.

    Thanks Again Everybody!!
    Larry

    Final File: SUMPRODUCT Function Find Multiplier Example.xlsm
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Assistance with Vlookup VBA Code Expression
    By larrysdime in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-03-2015, 02:00 PM
  2. [SOLVED] sumproduct formula using a wildcard expression
    By maacmaac in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2014, 02:39 AM
  3. How to insert a cell reference into a sumproduct expression?
    By Steve Rutkey in forum Excel General
    Replies: 13
    Last Post: 05-28-2011, 08:55 PM
  4. Sumproduct formula:Empl Code Payroll Code Amount
    By bluemeg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2009, 02:08 PM
  5. [SOLVED] sumproduct in code
    By papa jonah in forum Excel General
    Replies: 2
    Last Post: 03-16-2005, 03:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1