worksheetfunction question

[Guest]

Sub rework()
Dim ans As Variant
Dim ans2 As Variant
Dim var As Variant

ans = InputBox("Which advisor do you wish to use?")
ans2 = InputBox("Which payment method do you wish to use?")

var = WorksheetFunction.SumProduct(--(Range("A1:A10000") = ans),
--(Range("H1:H10000") = ans2))

MsgBox (var & " cases match your criteria.")
End Sub

I'm trying to work out how to use WorksheetFunction so I can make some
macro's that can calculate data without having to enter the formulas into the
spreadsheet. I tried off with .Index and got that to work quite easily, but
..SumProduct would be the most useful for me but i'm unsure how to make them
different to than if I was typing the formula into a cell...

 

[Don Guillett]

try this. Sumproduct does NOT work like other functions.

Sub rework()
Dim ans As Variant
Dim ans2 As Variant
Dim var As Variant

ans = InputBox("Which advisor do you wish to use?")
ans2 = InputBox("Which payment method do you wish to use?")
var = Application.Evaluate("SUMPRODUCT((A1:A10000 =" & ans & ")*(H1:H10000="
& ans2 & "))")
MsgBox (var & " cases match your criteria.")
'Range("j1") = var
End Sub

 

[Guest]

var = Application.Evaluate("SUMPRODUCT((A1:A10000 =" & ans & ")*(H1:H10000="

& ans2 & "))")

Tried, this seems to error, although the code doesn't debug until the next
line when it tries to show the msgbox and gives me error 13 as type mismatch.
Debugging it and having a look shows me that this line gives the variable
"var" the value "Error 2029". Did a quick look on google, and found a page
that said that this was a problem which sometimes occured on Excel 2002
normal edition, which confused me further since i'm using Excel 2003
Professional Edition.

Do I need to alter something in VBA to get this to work? (I know theres
loads of references that I might need to activate some of these to get
certain things to work)

Also, you mentioned that Sumproduct doesn't work like other functions. Does
this mean that Worksheetfunction.SumProduct doesn't work at all? or only when
i'm doing an actual Sumproduct rather than converting statements that produce
arrays of True/False into 0/1s?

 

[Guest]

Error 2029 is a NAME error, so you must be passing a string/alpha numeric
response to the inputbox. If you pass an alpha numeric answer to both of
the input boxes rather than a number you would need the code to be:

Sub rework()
Dim ans As Variant
Dim ans2 As Variant
Dim var As Variant

ans = InputBox("Which advisor do you wish to use?")
ans2 = InputBox("Which payment method do you wish to use?")
var = Application.Evaluate("SUMPRODUCT((A1:A10000 =""" _
& ans & """)*(H1:H10000=""" & ans2 & """))")
MsgBox (var & " cases match your criteria.")
'Range("j1") = var
End Sub

this produces a formula to be evaluated like this:
SUMPRODUCT((A1:A10="AAA")*(H1:H10="HHH"))

If one of them will be numeric, then you will need to remove the extra
quotes for that answer.

 

[Guest]

I'm hoping to use this at some point in a userform, on any sheet a user
wants. This means that it needs to be able to work on values and numbers.

I was using the example of "AS" for advisor, and "SO" for payment method, so
thats why Don's suggestion failed. I then changed the columns to look at a
date, and a payment amount, and input 39098 for ans, and 20 for ans2, this
produced an error on yours, which I obviously expected due to the last line
in your post.

Would it be possible for this to work on Numeric and a String? or should I
put this 4 times (with and without extra quotes dependant differently for
each one), and get the code to choose the appropriate one depending on if
each variable is a String/Numeric?

 

[Guest]

Try this one:

Sub rework()
Dim ans As Variant
Dim ans2 As Variant
Dim var As Variant

ans = InputBox("Which advisor do you wish to use?")
ans2 = InputBox("Which payment method do you wish to use?")
if not isnumeric(ans) then
ans = """" & ans & """"
end if
if not isnumeric(ans2) then
ans2 = """" & ans & """"
End if
var = Application.Evaluate("SUMPRODUCT((A1:A10000 =" _
& ans & ")*(H1:H10000=" & ans2 & "))")
MsgBox (var & " cases match your criteria.")
'Range("j1") = var
End Sub

 

[Guest]

Cheers, works great

Try this one:

Sub rework()
Dim ans As Variant
Dim ans2 As Variant
Dim var As Variant

ans = InputBox("Which advisor do you wish to use?")
ans2 = InputBox("Which payment method do you wish to use?")
if not isnumeric(ans) then
ans = """" & ans & """"
end if
if not isnumeric(ans2) then
ans2 = """" & ans & """"
End if
var = Application.Evaluate("SUMPRODUCT((A1:A10000 =" _
& ans & ")*(H1:H10000=" & ans2 & "))")
MsgBox (var & " cases match your criteria.")
'Range("j1") = var
End Sub