Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Hello all,
Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Well, you could do this,
Function GetPerson(rng1 As Range, name1, rng2 As Range, name2, rng3 As Range) GetPerson = Application.Caller.Parent.Evaluate( _ "SUMPRODUCT((" & rng1.Address & "=""" & name1 & """)*" & _ "(" & rng2.Address & "=""" & name2 & """)*" & _ "(" & rng3.Address & "))") End Function but getPerson(D1:D10, "June", E1:E10, "Mark",F1:F10) hardly saves much IMO -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
I was thinking along the same lien, except my code doesn't work :( I think it
faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Thanks for your reply...
The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
That suggests that the value you are passing as officeNo or state is not a
number, or sillLevel is not a string. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Just some added:
Might as well just use strings to refer to the ranges. Bob's suggestion failed to use the optiona arguments for Address, and so they would refer to the activesheet and not the Staffing All Sites sheet (unless it was the activesheet). Also, just to highlight Bob's addition which you may have missed: since SkillLevel is a string, it needs to be in double quotes in the formula: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim r1 as String, r2 as String, r3 as String r1 = "'Staffing All Sites'!$P$1:$P$200" r2 = "'Staffing All Sites'!$Q$1:$Q$200" r3 = "'Staffing All Sites'!$D$1:$D$200" getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")") End Function Here is an example of evaluating the string in the immediate window r1 = "'Staffing All Sites'!P1:P200" r2 = "'Staffing All Sites'!Q1:Q200" r3 = "'Staffing All Sites'!D1:D200" set empRange = Activesheet.Range("M1:M200") officeNo = 5 State = 6 SkillLevel = "A" ? "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" 'Produces: SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All Sites'!Q1:Q200=6)*('Staffing All Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200) -- Regards, Tom Ogilvy "kMan" wrote: Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Thank you Bob and Tom for your help.... I think the suggestions you've made
have stopped the function falling over on the first line. Yes I have included the string in double quotes. just some observations first: "empRange" will also be from the "Staffing All Sites" worksheet (not the active worksheet; the column range will vary in that worksheet). Tom, I have made the adjustments as per your suggestions. The funciton is now falling over on the "Evaluate" line. In Excel I am getting the following error message: "Moving or deleting cells caused an invalid cell reference, or function is returning a reference error". The debugger reports "Error 2023" on this line.... I am looking into this error, can you suggest something? Thanks "Tom Ogilvy" wrote: Just some added: Might as well just use strings to refer to the ranges. Bob's suggestion failed to use the optiona arguments for Address, and so they would refer to the activesheet and not the Staffing All Sites sheet (unless it was the activesheet). Also, just to highlight Bob's addition which you may have missed: since SkillLevel is a string, it needs to be in double quotes in the formula: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim r1 as String, r2 as String, r3 as String r1 = "'Staffing All Sites'!$P$1:$P$200" r2 = "'Staffing All Sites'!$Q$1:$Q$200" r3 = "'Staffing All Sites'!$D$1:$D$200" getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")") End Function Here is an example of evaluating the string in the immediate window r1 = "'Staffing All Sites'!P1:P200" r2 = "'Staffing All Sites'!Q1:Q200" r3 = "'Staffing All Sites'!D1:D200" set empRange = Activesheet.Range("M1:M200") officeNo = 5 State = 6 SkillLevel = "A" ? "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" 'Produces: SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All Sites'!Q1:Q200=6)*('Staffing All Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200) -- Regards, Tom Ogilvy "kMan" wrote: Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
do something like
s = "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" debug.print s getEmployeeOnSkill = Evaluate(s) then look in the immediate window for the formula that is produced. copy it from the immediate window and see if it can be legitimately used in a cell. If not, fix it to produce a good formula. -- Regards, Tom Ogilvy "kMan" wrote: Thank you Bob and Tom for your help.... I think the suggestions you've made have stopped the function falling over on the first line. Yes I have included the string in double quotes. just some observations first: "empRange" will also be from the "Staffing All Sites" worksheet (not the active worksheet; the column range will vary in that worksheet). Tom, I have made the adjustments as per your suggestions. The funciton is now falling over on the "Evaluate" line. In Excel I am getting the following error message: "Moving or deleting cells caused an invalid cell reference, or function is returning a reference error". The debugger reports "Error 2023" on this line.... I am looking into this error, can you suggest something? Thanks "Tom Ogilvy" wrote: Just some added: Might as well just use strings to refer to the ranges. Bob's suggestion failed to use the optiona arguments for Address, and so they would refer to the activesheet and not the Staffing All Sites sheet (unless it was the activesheet). Also, just to highlight Bob's addition which you may have missed: since SkillLevel is a string, it needs to be in double quotes in the formula: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim r1 as String, r2 as String, r3 as String r1 = "'Staffing All Sites'!$P$1:$P$200" r2 = "'Staffing All Sites'!$Q$1:$Q$200" r3 = "'Staffing All Sites'!$D$1:$D$200" getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")") End Function Here is an example of evaluating the string in the immediate window r1 = "'Staffing All Sites'!P1:P200" r2 = "'Staffing All Sites'!Q1:Q200" r3 = "'Staffing All Sites'!D1:D200" set empRange = Activesheet.Range("M1:M200") officeNo = 5 State = 6 SkillLevel = "A" ? "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" 'Produces: SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All Sites'!Q1:Q200=6)*('Staffing All Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200) -- Regards, Tom Ogilvy "kMan" wrote: Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
Thanks a mil Tom and Bob.... it was a stupid space character. It works if I
declare the ranges as: r1 = "' Staffing All Sites'!$P$1:$P$200" with a space character after '... No idea why, but good bye you cruel, cruel world.... :) "Tom Ogilvy" wrote: do something like s = "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" debug.print s getEmployeeOnSkill = Evaluate(s) then look in the immediate window for the formula that is produced. copy it from the immediate window and see if it can be legitimately used in a cell. If not, fix it to produce a good formula. -- Regards, Tom Ogilvy "kMan" wrote: Thank you Bob and Tom for your help.... I think the suggestions you've made have stopped the function falling over on the first line. Yes I have included the string in double quotes. just some observations first: "empRange" will also be from the "Staffing All Sites" worksheet (not the active worksheet; the column range will vary in that worksheet). Tom, I have made the adjustments as per your suggestions. The funciton is now falling over on the "Evaluate" line. In Excel I am getting the following error message: "Moving or deleting cells caused an invalid cell reference, or function is returning a reference error". The debugger reports "Error 2023" on this line.... I am looking into this error, can you suggest something? Thanks "Tom Ogilvy" wrote: Just some added: Might as well just use strings to refer to the ranges. Bob's suggestion failed to use the optiona arguments for Address, and so they would refer to the activesheet and not the Staffing All Sites sheet (unless it was the activesheet). Also, just to highlight Bob's addition which you may have missed: since SkillLevel is a string, it needs to be in double quotes in the formula: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim r1 as String, r2 as String, r3 as String r1 = "'Staffing All Sites'!$P$1:$P$200" r2 = "'Staffing All Sites'!$Q$1:$Q$200" r3 = "'Staffing All Sites'!$D$1:$D$200" getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")") End Function Here is an example of evaluating the string in the immediate window r1 = "'Staffing All Sites'!P1:P200" r2 = "'Staffing All Sites'!Q1:Q200" r3 = "'Staffing All Sites'!D1:D200" set empRange = Activesheet.Range("M1:M200") officeNo = 5 State = 6 SkillLevel = "A" ? "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" 'Produces: SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All Sites'!Q1:Q200=6)*('Staffing All Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200) -- Regards, Tom Ogilvy "kMan" wrote: Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct in VBA
That must be because the worksheet name has a leading space.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... Thanks a mil Tom and Bob.... it was a stupid space character. It works if I declare the ranges as: r1 = "' Staffing All Sites'!$P$1:$P$200" with a space character after '... No idea why, but good bye you cruel, cruel world.... :) "Tom Ogilvy" wrote: do something like s = "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" debug.print s getEmployeeOnSkill = Evaluate(s) then look in the immediate window for the formula that is produced. copy it from the immediate window and see if it can be legitimately used in a cell. If not, fix it to produce a good formula. -- Regards, Tom Ogilvy "kMan" wrote: Thank you Bob and Tom for your help.... I think the suggestions you've made have stopped the function falling over on the first line. Yes I have included the string in double quotes. just some observations first: "empRange" will also be from the "Staffing All Sites" worksheet (not the active worksheet; the column range will vary in that worksheet). Tom, I have made the adjustments as per your suggestions. The funciton is now falling over on the "Evaluate" line. In Excel I am getting the following error message: "Moving or deleting cells caused an invalid cell reference, or function is returning a reference error". The debugger reports "Error 2023" on this line.... I am looking into this error, can you suggest something? Thanks "Tom Ogilvy" wrote: Just some added: Might as well just use strings to refer to the ranges. Bob's suggestion failed to use the optiona arguments for Address, and so they would refer to the activesheet and not the Staffing All Sites sheet (unless it was the activesheet). Also, just to highlight Bob's addition which you may have missed: since SkillLevel is a string, it needs to be in double quotes in the formula: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim r1 as String, r2 as String, r3 as String r1 = "'Staffing All Sites'!$P$1:$P$200" r2 = "'Staffing All Sites'!$Q$1:$Q$200" r3 = "'Staffing All Sites'!$D$1:$D$200" getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")") End Function Here is an example of evaluating the string in the immediate window r1 = "'Staffing All Sites'!P1:P200" r2 = "'Staffing All Sites'!Q1:Q200" r3 = "'Staffing All Sites'!D1:D200" set empRange = Activesheet.Range("M1:M200") officeNo = 5 State = 6 SkillLevel = "A" ? "SUMPRODUCT((" & r1 & "=" _ & officeNo & ")*(" & r2 & "=" & state _ & ")*(" & r3 & "=""" & skillLevel & """)*" _ & empRange.Address(1,1,xlA1,True) & ")" 'Produces: SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All Sites'!Q1:Q200=6)*('Staffing All Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200) -- Regards, Tom Ogilvy "kMan" wrote: Thanks for your reply... The function seems to breakdown on the first Set line (Set rngeOffice....). If I put a breakpoint on the secondline, doesn't get to it and I get a "A value used in the formula is a wrong data type" error in Excel.... I did consider the downside of declaring some of the ranges inside the function... They remain fairly static and this is a one-off exercise, so decided to make it easier on the eyes instead "Bob Phillips" wrote: Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "=" & officeNo & ")*" & _ "(" & rngeState.Address & "=" & state & ")*" & _ "(" & rngeSkill.Address & "=""" & skillLevel & """)*" & _ empRange.Address & ")") End Function But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200, or D1:D200 change, then the UDF does not recalculate. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kMan" wrote in message ... I was thinking along the same lien, except my code doesn't work :( I think it faults at the first line where I am attempting to set the range (a range in a different worksheet). Can you please help me? Thanks a mil. Function getEmployeeOnSkill(officeNo As Integer, state As Integer, skillLevel As String, empRange As Range) As Variant Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200") Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200") Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200") getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" & officeNo & ")*(" & rngeState & "=" & state _ & ")*(" & rngeSkill & "=" & skillLevel & ")*" & empRange & ")") End Function The aim of the function is to return sum in the empRange, given matches in the other three columns... The three columns stay fixed, empRange moves around a bit..... Cheers "Gary''s Student" wrote: Function getperson(s1 As String, s2 As String) As Integer dq = Chr(34) p1 = "=sumproduct((D1:D10=" & dq p2 = dq & ")*(E1:E10=" & dq p3 = dq & ")*(F1:F10))" func = p1 & s1 & p2 & s2 & p3 getperson = Evaluate(func) End Function Use as: =getperson("June","Mark") -- Gary''s Student - gsnu200740 "kMan" wrote: Hello all, Appreciate your help. Basically, I have a looong equation in an excel cell, mainly utilising Sumproduct function. I.e. (simplified) Cell A4: = sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 )) Problem is that function above is repeated for multiple entities (i.e. John, Lisa, Nick.... inadidition to Mark) and it gets rather messy. I'm hoping to define a VBA function, such as: getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark) to make it easy for the person auditing the worksheet (and the second variant so that I don't have to pass the constant references everytime).... Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct Help | Excel Discussion (Misc queries) |