[[20120727155709]]　『VBAでSUMPRODUCT関数を使うとエラーが出る』（uniuni）　ページの最後に飛ぶ

『VBAでSUMPRODUCT関数を使うとエラーが出る』（uniuni）Excel2003

こんにちは。
SUMPRODUCT関数を使いたくて下記のようにしましたがエラーが出ます。
どのように直したら良いのでしょうか？

Range("F5").FormulaR1C1 = WorksheetFunction.SumProduct((Workbooks("today.xls").Sheets("データ").Range("H2:H200") = Range("A20")) * (Workbooks("today.xls").Sheets("データ").Range("N2:N200") = "明細"))

 この内容で正しく処理できるかどうかはみていないけど、一般論として

 左辺が　セル.Formula ないしは セル.FormulaR1C1 であれば、右辺は　ワークシート上に記述する関数式そのものを"  " で囲んで指定する。
 結果は、セルに、その式がセットされる。

 一方、右辺が WorksheetFunction.シート関数名　ということは、この処理結果を、結果の値として返す。
 なので、 変数 = WorksheetFunction.シート関数名(・・・・・) ということになる。

 やりたいのはどっち？

 (ぶらっと)

 ぶつかりましたが、式の設定(FORMULAへの代入)であれば
 Range("F5").FormulaR1C1 = "=SumProduct(([today.xls]データ!R2C8:R200C8=R20C1)*([today.xls]データ!R2C14:R200C14=""明細""))"
 ではないでしょうか。

 VBA での計算だったら別の回答をお待ち下さい。
 (Mook)

 たぶんこのへんだろうと。
[[20080206141924]]
 BJ

式が長いので同じシートのセルで考えます。
Range("F5").Value = WorksheetFunction.SumProduct((Range("H2:H200") = Range("A20")) * (Range("N2:N200") = "明細"))

上記は「実行時エラー１３、型が一致しません」

でも、下記なら通る。
Range("F5").Value = WorksheetFunction.SumProduct([(H2:H200=A20)*(N2:N200="明細")])

[]の中身をセルに入力した参照式のような形にもっていけば計算は可能なはずです。

ただしこの書き方だと[]の中身は変数を絡めた書き方が出来ないので汎用性が低い。
本校の過去ログ、[[20070212134849]]　『Evaluate Method の研究』（seiya）から、
式内の　[　および　]　はEvaluate関数のショートカットであると考えられるので、
次のようにも書けます。

Range("F5").Value = WorksheetFunction.SumProduct(Evaluate("(H2:H200=A20)*(N2:N200=""明細"")"))

Evaluate関数の引数の文字列をセル参照式の形式にしていけば計算が可能なはずです。
（みやほりん）

みなさん返信ありがとうございます。
初心者なのでよくわからない部分もありますが･･･
少し訂正させてください。

【誤】
Workbooks("today.xls").Sheets("データ").Range("H2:H200")

【正】
Dim today as String
Dim LastR as Long
Workbooks("明細_" & today & ".xls").Sheets("データ").Range("H2:H" & LastR)

変数を使っています。
みなさんにアドバイス頂いたもので試してみたのですが、うまくいきません。
私が書いた式が下記です。
Range("A20")を"データ"に変えました。

Range("F5").Value = WorksheetFunction.SumProduct(Evaluate("(workbooks("明細_" & today & ".xls").sheets("データ").range("H2:H" & LastR)=""データ"")*(workbooks("明細_" & today & ".xls").sheets("データ").range("N2:N" & LastR)=""明細"")"))

Range("F5").FormulaR1C1"=SUMPRODUCT((workbooks("明細_" & today & ".xls").sheets("データ").range("H2:H" & LastR)=""データ"")*(workbooks("明細_" & today & ".xls").sheets("データ").range("N2:N" & LastR)=""明細""))"

いずれもエラーが出ます。
どこが駄目なのかよくわかりません。
アドバイス宜しくお願い致します。

=[Book1.xls]Sheet1!$A$1
をFormulaプロパティの方法で書くと、
Range("F5").Formula = "=[Book1.xls]Sheet1!$A$1"
Evaluateの方法なら、
Range("F5").Value = Evaluate("[Book1.xls]Sheet1!$A$1")

つまり、セル参照式としての文字列が入るので、
ここで説明するよりも、セルで一旦入力した式を参考にしたほうがいいですね。
(みやほりん)(-_∂)b

みやほりんさん

返信ありがとうございます。
いまいち理解できないのですが･･･

変数が入ると「.Value =」や「.Formula =」の式が使えないということでしょうか？

私は、
＞セルで一旦入力した式を参考にしたほうがいいですね。
と書いています。

Evaluateの引数は参照式や関数式を表現する文字列です。
Evaluateの引数にしようとしているものが、目的の数式と同じ文字列の
ならびになるのか、Msgbox　などで出力して試してみるのがいいでしょう。

例えば、このような感じになるはずです。

MsgBox "([明細_" & today & ".xls]データ!H2:H" & LastR & "=""データ"")*([明細_" & today & ".xls]データ!N2:N" & LastR & "=""明細"")"

上記のMsgboxで適当な文字列が表示されるようなら、
次の段階に進んでやってみます。

Range("F5").Value = WorksheetFunction.SumProduct(Evaluate("([明細_" & today & ".xls]データ!H2:H" & LastR & "=""データ"")*([明細_" & today & ".xls]データ!N2:N" & LastR & "=""明細"")"))

コードを作るのももちろん大事です。
が、どこが悪いのか検証するスキルも付けていかないと、
理解は進みにくいですよね？

（みやほりん）

コメント返信：

[ 一覧(最新更新順) ]

[[20120727155709]] 『VBAでSUMPRODUCT関数を使うとエラーが出る』（uniuni） ページの最後に飛ぶ

[[20120727155709]]　『VBAでSUMPRODUCT関数を使うとエラーが出る』（uniuni）　ページの最後に飛ぶ