excel VBAで計算式を入力したい

質問

下記の計算式をVBAから入力したいのですが、うまくいきません。
=SUMPRODUCT((稼動データ!F2:F89="C")*(稼動データ!E2:E89={49,65,66,67,68,70,73,74,93,8106,8169,8192,8194,8561})*稼動データ!I2:I89)

文字列は、""で囲むのは判っているのですが。。。
いくつか試しましたがうまくいきません。
Range("M43").Formula = の後にどのような式を入力すればうまくいきますか？

よろしくお願いします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

#1さんの回答で間違いありませんが、単に、それは、「F2:F89="C"」の"C" が違っているだけです。

一応、A1型で書いておきます。

Range("M43").Formula = "=SUMPRODUCT((稼動データ!F2:F89=""C"")*(稼動データ!E2:E89={49,65,66,67,68,70,73,74,93,8106,8169,8192,8194,8561})*稼動データ!I2:I89)"

とすればよいです。

なお、複雑な配列数式を行うなら、マクロ自体で計算をさせたほうが良いです。配列数式は、ワークシートのメモリを消費しますから、数が多くなると、不都合が出てくることもあります。

お礼

Wendy02さん。
前にも私の質問に回答していただいたことがありますが、いつも勉強になります。

> Range("M43").Formula = "=SUMPRODUCT((稼動データ!F2:F89=""C"")*(稼動データ!E2:E89={49,65,66,67,68,70,73,74,93,8106,8169,8192,8194,8561})*稼動データ!I2:I89)"
マクロの記録では、A1型にはならないため上記回答ですっきりしました。

回答No.4

Wendy02

#3の回答者です。

普通は、以下のような内容のマクロは書かずに、ひとつの列に対して、Offset で処理しますが、あえて、分かりやすくするために、範囲を3つに分けて、数式をマクロに置き換えてみました。

Sub Test2()
　　'=SUMPRODUCT((稼動データ!F2:F89=""C"")*(稼動データ!E2:E89={49,65,66,67,68,70,73,74,93,8106,8169,8192,8194,8561})*稼動データ!I2:I89)"
　　Dim rng1 As Range
　　Dim rng2 As Range
　　Dim rng3 As Range
　　Dim i As Long
　　Dim arData As Variant
　　Dim Ret As Variant
　　Dim dblSum As Double
　　With Worksheets("稼動データ")
　　arData = Array(9, 65, 66, 67, 68, 70, 73, 74, 93, 8106, 8169, 8192, 8194, 8561)
　　Set rng1 = .Range("F2:F89") '1列のこと
　　Set rng2 = .Range("E2:E89")
　　Set rng3 = .Range("I2:I89")
　　For i = 1 To rng1.Rows.Count
　　　If rng1.Cells(i, 1).Value = "C" Then
　　　　 Ret = Application.Match(rng2.Cells(i, 1).Value, arData, 0)
　　　　If IsNumeric(Ret) Then
　　　　　dblSum = dblSum + rng3.Cells(i, 1).Value
　　　　End If
　　　End If
　　Next i
　　　　.Range("M43").Value = dblSum
　　End With
　　Set rng1 = Nothing: Set rng2 = Nothing: Set rng3 = Nothing
End Sub

お礼

判りやすく説明していただいたのですが、理解に時間がかかりそうです。じっくり理解していきたいと思います。
今回もありがとうございました。

回答No.2

noname#64582

私も同様に困ったことがあります。
いろいろ調べまわった結果、
VBAで、Sumproduct を通常の使用法（積の和）として使う以外は、Evaluate　というのを使う、ということがわかり、これで解決しました。

ご質問のケースも、複数条件を満たすものの合計という使い方だと思われますので該当するのではないでしょうか。

以下URLの、回答（■12034）にありました。
http://hpcgi1.nifty.com/kenzo30/b_cbbs/cbbs.cgi?mode=al2&namber=12033&rev=&no=0&P=R&KLOG=77

お礼

> Evaluate　というのを使う、ということがわかり、これで解決しました。
Evaluateメソッドは知っていたのですが、計算式では無く値が入力されてしまう為使ってませんでした。
回答ありがとうございました。

回答No.1

deecyan

" で囲まれた"　は　""　と書きます。
多分これが出来てないとおもいます。

わからなかったら
ツール→マクロ→新しいマクロの記録で
その式をいれてみたらいいです
下記の様に記録されているので　そこをちょこちょこっと変えましょう

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((稼動データ!R[-8]C[2]:R[79]C[2]=""C"")*(稼動データ!R[-8]C[1]:R[79]C[1]={49,65,66,67,68,70,73,74,93,8106,8169,8192,8194,8561})*稼動データ!R[-8]C[5]:R[79]C[5])"

お礼

> わからなかったら
> ツール→マクロ→新しいマクロの記録で
> その式をいれてみたらいいです
そうですよね。一番手っ取り早いかもしれません。すっかり忘れていました。
回答ありがとうございます。助かりました。