Sumproduct syntax for use in a Userform

    Hi again,


    Need your expert help on the following:
    I have a formula in a certain sheet, that works perfectly:

    Code
    =SUMPRODUCT(--(Orders!$M$2:$M$3000=Budget!D2),--(Orders!$B$2:$B$3000=Budget!I2),(Orders!$AC$2:$AC$3000))


    Now I need to convert this formula into something that can work in a userform, based on certain selections entered by the user.
    In the userform there are two text boxes, one is called TB_Unit_Name and the other is called TB_Unit_Model.
    There is another text box called TB_Total_Units_On_Order


    What I am trying to do is something that looks like the code below (which only represents what I am aiming for) but I don't know how to write it in VBA.

    Code
    me.TB_Total_Units_on_Order.value =SUMPRODUCT(--(Orders!M2:M3000=me.TB_Unit_Name.value),--(Orders!B2:B3000=me.TB_Unit_Model.value),(Orders!AC2:AC3000))


    What I need your help with, is the proper syntax for this calculation (or a different solution?).


    BTW, just to clarify, whenever the form data is changing (based on user selection) and the data is in TB_Unit_Name & TB_Unit_Model changes - I need the form to show the total quantities that were ordered based on those two criteria.
    In the data sheet "orders" there can be many lines that meet the two criteria, hence the need for sumproduct calculation.


    I hope I have made myself clear :?
    T.I.A.

    Re: Sumproduct syntax for use in a Userform


    Not tested, but try this

    Code
    Me.TB_Total_Units_on_Order.Value = Evaluate("SUMPRODUCT(--(Orders!M2:M3000=" & Me.TB_Unit_Name.Value & "),--(Orders!B2:B3000=" & Me.TB_Unit_Model.Value & "),(Orders!AC2:AC3000))")

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776709

    Not tested, but try this

    Code
    Me.TB_Total_Units_on_Order.Value = Evaluate("SUMPRODUCT(--(Orders!M2:M3000=" & Me.TB_Unit_Name.Value & "),--(Orders!B2:B3000=" & Me.TB_Unit_Model.Value & "),(Orders!AC2:AC3000))")


    Thanks very much for the swift reply.
    Not really yielding any result in my file, but you gave me a good direction, so I'll keep playing with it until I get it right.
    :cheers:

    Re: Sumproduct syntax for use in a Userform


    I am not familiar with Evaluate, but you could possibly use application.worksheetfunction to achieve the desired result.

    Re: Sumproduct syntax for use in a Userform


    Do you mean it errors or yields an unexpected result or yields zero or something else?


    Infomage - more or less the same idea, it probably just comes down to nailing the syntax.

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776717

    Do you mean it errors or yields an unexpected result or yields zero or something else?


    StephenR,
    Actually you are correct in your question and I appreciate your followup on this, and apologize for not mentioning this info initially..
    I get an error message "Could not set the value property. Type mismatch".



    Infomage: Thanks for your suggestion but I not sure this is the solution, in light of the error message

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776719

    Could you post a workbook?


    I am afraid not.
    1. It is a a foreign language that will only confuse you
    2. It contains confidential commercial data.


    I thought about creating a mock-up file out of my original file, but there are simply too many variables to change/remove.
    If there is no other way around it, I will try to create a demo file from scratch.

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776722

    I think just need a file with a userform with three textboxes and a few rows of dummy data? Will probably speed up resolution.


    Yeah... You are right. I was thinking the same thing...
    I Will create something that resembles my data structure and post it later today.

    Re: Sumproduct syntax for use in a Userform


    Just a quote mark issue I think. I may have got the ranges the wrong way round

    Code
    Me.TextBox3.Value = Evaluate("SUMPRODUCT(--(Orders!A2:A38=""" & Me.TextBox1.Value & """),--(Orders!B2:B38=""" & Me.TextBox2.Value & """),Orders!D2:D38)")

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776730

    Just a quote mark issue I think. I may have got the ranges the wrong way round

    Code
    Me.TextBox3.Value = Evaluate("SUMPRODUCT(--(Orders!A2:A38=""" & Me.TextBox1.Value & """),--(Orders!B2:B38=""" & Me.TextBox2.Value & """),Orders!D2:D38)")


    Stephen,
    This works like a charm :wowee:
    Thank you!


    I never know how to build the syntax of the Evaluate formula, either with fixed ranges and even more so - when there are variables involved (like a relative row reference, if I am running the formula from within a loop).
    Where can I read/learn about this?
    It really feels awkward to come here for help each time I need to build such a formula.
    I always get the double-quotes wrong and I wasn't able to find any place on line, that teaches the logic behind it...
    Can you - perhaps - point me in the right direction?


    Thanks again !

    Re: Sumproduct syntax for use in a Userform


    Quote from StephenR;776733

    The basic rule about quotes inside formulae in VBA is that you have to double them up, e.g. this

    Code
    Range("A1").Formula = "=if(a1=""a"",""yes"",""no"")"

    will produce
    =IF(A1="a","yes","no")


    On Evaluate, there is a page here at Oz http://www.ozgrid.com/forum/showthread.php?t=52372


    Thanks! I understand what you say about the "" but this answers only a part of my questions :(
    I am also familiar with the link you provided, but as far as I can see - it does not provide an explanation regarding the HOW to write an elaborate "Evaluate" formula.
    I tried to figure out why you added all the double quotes in the formula you gave me above, but could not figure out the "rules" (where to add them, and why).
    This gets even more difficult when trying to replace fixed a row number in a range, with a relative one (i.e. the when running a loop and using the loop numerator as row number).
    In such cases, there are a million double quotes all over the place...


    Is there like a "secret book" somewhere, teaching this?
    There are experts here, who were always happy help me whenever I needed to build a specific "Evaluate" formula (which is great and highly appreciated), but I rather get a "fishing pole" than a "fish" :)

    Re: Sumproduct syntax for use in a Userform


    Sadly there is no magic bullet. I'm not a formula expert but hang around Oz for a while and you will pick up hints from those who are, and then there is always Google...

    Re: Sumproduct syntax for use in a Userform


    You've really got two separate things here. An "Evaluate formula" is basically just a formula as you would enter it into a cell (though you don't require the leading = sign with Evaluate, and you are limited to 255 characters).


    So Evaluate itself is somewhat irrelevant to the syntax - you simply need to create a string that would be a valid formula in a cell.


    However, because VBA delimits strings with quote marks, you need to double up any literal quotes that are within that string so that VBA doesn't think you are terminating the string at each quote.


    So to take Stephen's example and add a couple of variables, let's say we want to produce this formula in B1:


    Code
    =IF(A1="a","yes","no")


    where the a, yes and no are all to be variable values - to wit:


    Code
    var1 = "a"
var2 = "yes"
var3 = "no"


    that we want to add into the formula.


    So for starters, we would need to concatenate the variables into a string so that we get their values and not their literal names. In other words, we can't do this:

    Code
    Range("B1").Formula = "=IF(A1=var1,var2,var3)"


    because if you enter that exact text as a formula, Excel would have no idea what var1, var2 and var3 are and you'd get a #NAME? error.


    That means that our first step has to be to concatenate the values of the variables and not their names to the formula string:

    Code
    Range("B1").Formula = "=IF(A1=" & var1 & "," & var2 & "," & var3 & ")"


    That is a good start, but if you ran it you would see this in B1:
    =IF(A1=a,yes,no)
    and that will produce a #NAME? error. As we know from our original formula, we actually need:
    =IF(A1="a","yes","no")
    So our next step is to add the quotes into the formula string we have (additions in blue below)


    "=IF(A1="" & var1 & "","" & var2 & "","" & var3 & "")"


    but as we know, any quotes within the formula must be doubled so the blue bits actually need to be:


    "=IF(A1=""" & var1 & """,""" & var2 & """,""" & var3 & """)"


    You can also perform the logic in reverse by starting with the formula result you want, as a literal string, then doubling up any quotes:


    Code
    "=IF(A1=""a"",""yes"",""no"")


    then replace each of the literal values (a, yes, no) with:

    "& variable_name & "

    to get:

    Code
    "=IF(A1=""" & var1 & """,""" & var2 & """,""" & var3 & """)


    Note: if you find it easier, you can actually use another variable for the quote symbol:

    Code
    sQuote = Chr(34) ' - this is the code for "
    Range("B1").Formula = "=IF(A1=" & sQuote & var1 & sQuote & "," & sQuote & var2 & sQuote & "," & sQuote & var3 & sQuote & ")"


    I don't know if that helps or makes it worse?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Sumproduct syntax for use in a Userform


    @ StephenR:
    I searched the web up and down, and could not find a "manual" for this :(


    rory:
    WOW WOW WOW... I am speechless and awed by your post! This is extremely helpful (and I am sure not just for me).
    I will have to do a lot of testing in order to get some proficiency in the process you described, but I assume that eventually I will "get there".


    The only thing not clear IMHO (and forgive my rudeness) is how to refer to dynamic Row or Column numbers.
    So I.S.O. "[COLOR="#FF0000"]A1[/COLOR]" - I want to deal with [COLOR="#FF0000"]"A" & i [/COLOR](for instances).
    I assume the same logic should apply, but I am afraid of getting lost with so many double quotes.


    Thank you again for taking all the time and making all the effort, writing this "manual"!!!!!!!


    God's bless
    P.

    Re: Sumproduct syntax for use in a Userform


    Row numbers are relatively simple as you don't need to worry about enclosing the numbers in quotes - all you need is to replace the number with:
    "& row_number_variable_name & "


    so

    Code
    Range("B1").Formula = "=A1*2"


    using lRow as a variable, would become

    Code
    Range("B1").Formula = "=A" & lRow & "*2"


    In many such situations you may also find that using R1C1 style references makes the code a lot simpler.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Sumproduct syntax for use in a Userform


    Quote from rory;776846

    Row numbers are relatively simple as you don't need to worry about enclosing the numbers in quotes - all you need is to replace the number with:
    "& row_number_variable_name & "



    I think your last posts should be "pinned" somewhere because they look like the building blocks of VBA formulas handling!


    :drum: You Rock !
    Thanks !

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login