sumproductの計算式で「エラー2015」（ローカルウィンドウにて）が表示（Excel2007、2003）

質問

2つの条件に合致する数量の合計を出したい為、sumproduct関数を使って計算結果を算出しようと考えています。

sumproduct関数を使った例をwebからいくつも見て下記モジュールを書きましたが、「ans」（sumproduct関数での計算結果部分）でローカルウィンドウに「エラー2015」が表示され結果が出ません。
（試しにセル上に直接、sumproduct関数を使って計算結果が出るか確認したところ、結果表示されます。）

「"」や「&」の付け方に問題があるかと思い、いろいろなパターンで試しましたが同様です。

修正記述がお分かりの方がいらっしゃいましたら、お言葉をいただけると幸いです。

宜しくお願いいたします。

－－－－－－－－－－－－－－－－－－－－－－－－
Sub culiturate()

Dim str1, str2, str3 As Range

Rows("2:2").Select
cate = Selection.Find(what:="分類", lookat:=xlWhole).Column
SP = Selection.Find(what:="特記", lookat:=xlWhole).Column
kazu = Selection.Find(what:="数量", lookat:=xlWhole).Column

saigo = Cells(Rows.Count, cate).End(xlUp).Row

Set str1 = Range(Cells(3, cate), Cells(saigo, cate))
Set str2 = Range(Cells(3, SP), Cells(saigo, SP))
Set str3 = Range(Cells(3, kazu), Cells(saigo, unit))

ans = Evaluate("sumProduct((" & str1.Address & "=" & "2BB" & ") * (" & str2.Address & "=" & "7RR" & ")," & str3.Address & ")")　'←ここでエラー2015になる

End Sub

回答No.2

hige_082 · ベストアンサー

＞「"」や「&」の付け方に問題があるかと思い、いろいろなパターンで試しましたが同様です。
そのとおりです、「"」の付け方に問題がありました
＞ans = Evaluate("sumProduct((" & str1.Address & "=" & "2BB" & ") * (" & str2.Address & "=" & "7RR" & ")," & str3.Address & ")")　'←ここでエラー2015になる
の
"sumProduct((" & str1.Address & "=" & "2BB" & ") * (" & str2.Address & "=" & "7RR" & ")," & str3.Address & ")"
の、部分を文字列で出力すると
sumProduct(($A$3:$A$5=2BB) * ($B$3:$B$5=7RR),$C$3:$C$5)
の様になり、エラー個所が見やすいと思います
sumProduct(($A$3:$A$5="2BB") * ($B$3:$B$5="7RR"),$C$3:$C$5)
とするためには
ans = Evaluate("sumProduct((" & str1.Address & "=" & """2BB""" & ") * (" & str2.Address & "=" & """7RR""" & ")," & str3.Address & ")")
と修正してください

他も修正してみたので、試してみて

Sub culiturate()

Dim str1 As String, str2 As String, str3 As String
Dim cate As Integer, SP As Integer, kazu As Integer, unit As Integer
Dim saigo As Long
Dim ans As Variant

cate = Rows("2:2").Find(what:="分類", lookat:=xlWhole).Column
SP = Rows("2:2").Find(what:="特記", lookat:=xlWhole).Column
kazu = Rows("2:2").Find(what:="数量", lookat:=xlWhole).Column

saigo = Cells(Rows.Count, cate).End(xlUp).Row

str1 = Range(Cells(3, cate), Cells(saigo, cate)).Address
str2 = Range(Cells(3, SP), Cells(saigo, SP)).Address
str3 = Range(Cells(3, kazu), Cells(saigo, unit)).Address

ans = Evaluate("sumProduct((" & str1 & "=""2BB"") * (" & str2 & "=""7RR"")," & str3 & ")") '←ここでエラー2015になる

MsgBox ans
End Sub

基本的な処理は変更していないので、これでエラーが出る場合は他に原因があると思います
変数str3をrangeからstringに変更してますが
次のEvaluate式を短くするために行っただけで
深い意味はありません

以上参考まで

お礼

hige_082様

回答ありがとうございます。

今回の原因は、範囲の変数の定義を各変数にしなかったことのようです。
（hige_082様が書いて下さったデバッグが起こる箇所の記述は、webで書込みをした記述以外で試したものでした。）

私が記述したもので、文字列にした場合の確認の仕方までふれて下さり、誤りが明確に分かりました。

アドバイスありがとうございます。
感謝します。

回答No.3

Wendy02

こんばんは。

#1で書いた、
「本当は、そのような数式を使わずに、セルひとつずつにループして計算するようにしたほうがよいです」
のコードを出して置きます。多少、スピードには難点がありますが、VBAらしさを求めても良いと思います。書き方によっては、もう少しスピードは上がります。

'-------------------------------------------
Sub Test2()
　　Dim rng As Range
　　Dim c As Range
　　Dim dbSum As Double
　　Const ARG1 As String = "2BB"
　　Const ARG2 As String = "7RR"
　　With ActiveSheet
　　　Set rng = .Range("B3", .Range("B65536"))
　　End With
　　Application.ScreenUpdating = False
　　For Each c In rng
　　　　If c.Value = Trim(ARG1) And _
　　　　　　c.Offset(, 2).Value = Trim(ARG2) Then
　　　　　　dbSum = dbSum + c.Offset(, 3).Value
　　　　End If
　　Next c
　　Application.ScreenUpdating = True
　　MsgBox dbSum
End Sub

回答No.1

Wendy02

こんばんは。

VBAのプロシージャで、Evaluate の中では、配列も同じですから、SUMで良いのです。ただ、VBAの数式の配列を使うというのは、上級のレベルになりますから、本当は、そのような数式を使わずに、セルひとつずつにループして計算するようにしたほうがよいです。

× Set str3 = Range(Cells(3, kazu), Cells(saigo, unit)) 配列数式なのに、別の範囲を入れたらエラーが発生します。
Set str3 = Range(Cells(3, kazu), Cells(saigo, kazu))

本来は、Set str3 = Cells(3, kazu).Resize(saigo - 2)　のように、Resize を利用したほうが確かです。

ans = Evaluate("SUM((" & str1.Address & "=""2BB"")*(" & str2.Address & "=""7RR"")*(" & str3.Address & "))")

お礼

Wendy02様

回答ありがとうございます。

誤りご指摘のstr3ですが、変数を誤って書いていました。
（「unit」を「kazu」に書き換えていたのですが、webでの投稿時に書き換えモレをしてしまいました。）

範囲の変数を省略せずに、各変数ごとに「as Range」と定義し、ansの「&」記述箇所を訂正したところ、計算結果が出るようになりました。

Wendy02様が案を出して下さったsum関数を使った式も、大変参考になるので有り難く思っています。