Re: Sumproduct in VBA

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

That must be because the work*** name has a leading space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kMan" <kMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D913BB54-5182-4946-B589-DD4E329E7B12@xxxxxxxxxxxxxxxx
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" work***
(not
the active work***; the column range will vary in that work***).

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 active*** and not the Staffing All Sites *** (unless it was
the
active***).

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 = Active***.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" <kMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C630D6CB-AFFE-40C8-81D8-470B4B3DC21C@xxxxxxxxxxxxxxxx
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 work***).

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 work*** (and
the second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?





.

Quantcast