VBAで関数式の値をセルに入力できるようにしたい。

質問

こんなマクロをマクロの記録で作ったのですが
SUMIF関数の数式をセルに入力するのでなく
値だけを入力するしたいのですがどのように
すればいいでしょうか?

Sub Macro4()

Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Range("N3").Select
Selection.AutoFill Destination:=Range("N3:O3"), Type:=xlFillDefault
Range("N3:O3").Select
Range("O5").Select
ActiveCell.FormulaR1C1 = "=SUMIF(出荷貼付け!C1,RC1,出荷貼付け!C5)"　←ここのところを値だけをセルに入力したい。

Selection.AutoFill Destination:=Range("O5:O978")
Range("O5:O978").Select
Range("O4").Select
End Sub

回答No.8

matsu_jun · ベストアンサー

matsu_junです。会社に行く前に読んでいます。
katope007さん、quit123さん、ご指摘ありがとうございます。おっしゃるとおりです。恥ずかしい。昨夜は少し寝ぼけていたようですね。以後注意します。

お二人のご指摘の通り、いままでのやり方では、セルO5からO978まで同じ値が入ってしまいます。どんどん複雑になっていきますが、今後のために「For～Next」文を覚えるという意味合いを込めて、ご活用ください。
#5の回答を、以下のように書き換えてみてもらえませんか？

For i# = 5 To 978
Cells(i, 15).Value = Application.WorksheetFunction _
.SumIf(Worksheets("出荷貼付け").Range("A1:A5"), Worksheets("別シート").Cells(i, 1).Value, Worksheets("出荷貼付け").Range("E1:E5"))
Next i

上の式は、iの値を5から978まで1ずつ変化させながら、For文の下の行からNextの上の行までの処理を複数回実施する。という意味です。なお今回、Range("A1") というのを Cells(i, 1)と書き換えています。これは、前者ではセルの位置を文字として認識させているのに対し、後者では数式として認識させているという違いがあります。マクロの中でセルの参照を色々変化させたい時に用いると便利です。注意点は、「A1」が、列－行という書き順になっているのに対し、「Cells(i, 1)」は行－列の順に並べているという点と、Aは1、Bは2、Cは3といったように、アルファベットを数字で表記するという点です。

だらだらと、大変失礼いたしました。こうなると実際の記述としては、#1のpapayukaさんや#7のKenKen_SPの方法を利用された方が良いかもしれませんね。実際KenKen_SPの回答は私もいつも拝見しているのですが、舌を巻くことがしょっちゅうです。私のような"なんちゃって"と違って、きちんとされている方だと思います。私もよく参考にしています。

お礼

いろいろな方法を考えて頂いて感謝しています。
ありがとうございます。
こんな初心者にもわかるように。
おかげ様でよくわかりました。
これからも度々質問させて頂くことと思いますが
よろしくお願い致します。

補足

返答が遅くなってすみません。
初心者の私にとても、とても、親切に教えて頂きありがとうございます。
私はsumif関数をエラーはでるのでが、範囲設定をすると何かと設定し直さなければならないので次様にしていました。これが原因の様でした。
=SUMIF(出荷貼付け!$A:$A,$A5,出荷貼付け!$E:$E)
ウィザードでは#NUM!のエラーはでるのでが、
ちゃんとできるのです。
列を選択にしておくと何行入っても大丈夫だったので・・・。
そのために変な関数がマクロの記録に出たようです。

回答No.7

KenKen_SP

#1　papayuka さんの回答のように、一度計算させたあと、値に変換する方が簡単では？
オリジナルに一行手を入れるだけです。

Range("O5:O978").Select
Range("O4").Select
End Sub

この部分を

Range("O5:O978").Select
Selection.Value = Selection.Value
Range("O4").Select
End Sub

のように、　Selection.Value = Selection.Value　の一行を追加します。

奇妙に見えるかもしれませんが、セルがSelectされているなら、これで計算式の結果を値に変換できます。

お礼

ありがとうございます。
大変勉強になりました。
これからもよろしくお願い致します。
使わせて頂きます。

回答No.6

quit123

matsu_junさん、こんばんは。

いつも素晴らしい回答拝見しています。
ちょっと気になったので一言。

> Range("O5:O978").Value = Application.WorksheetFunction _
> .SumIf(Worksheets("出荷貼付け").Range("A1:A5"), Worksheets("別のシート").Range("A1").Value, Worksheets("出荷貼付け").Range("E1:E5"))

これって、Range("05:0978")に全部同じ値が入りませんか？

勘違いでしたらご容赦願います。

お礼

ありがとうございました。
とても勉強になりました。

これからもよろしくお願い致します。

回答No.5

matsu_jun

#2、#4のmatsu_junです。失礼しました。#4で結構とんちんかんな事を言っています。半分寝ていますね。私

> どちらにしても、SUMIF関数の利用方法については問題ないと思いますよ。記述としては以下のようになります。ついでに下の行とまとめてみました。

> Range("O5:O978").Value = Application.WorksheetFunction _
> .SumIf(Worksheets("出荷貼付け").Range("A1:A5"), Worksheets("別のシート").Range("A1").Value, Worksheets("出荷貼付け").Range("E1:E5"))

この部分だけ信用してください。失礼しました。

回答No.4

matsu_jun

#2のmatsu_junです。
私も少々思い違いをしていたようで、cocoku様の補足を見た後にもう一度質問のマクロを眺めていたら、補足で言うところの「別のシート!$A1」の値が、このシートのセルO5に入っていたのかと想像するのですが。

どちらにしても、SUMIF関数の利用方法については問題ないと思いますよ。記述としては以下のようになります。ついでに下の行とまとめてみました。

Range("O5:O978").Value = Application.WorksheetFunction _
.SumIf(Worksheets("出荷貼付け").Range("A1:A5"), Worksheets("別のシート").Range("A1").Value, Worksheets("出荷貼付け").Range("E1:E5"))

最初の質問の「セルO5」を生かすのであれば、

Range("O5:O978").Value = Application.WorksheetFunction _
.SumIf(Worksheets("出荷貼付け").Range("A1:A5"), ActiveSheet.Range("A1").Value, Worksheets("出荷貼付け").Range("E1:E5"))

となると思います。

回答No.3

katope007

間違っていたらすいません。自分で状況をマクロにあわせたら、集計はできました。

matsu_junさんのを見てみたんですが、
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.SumIf(Worksheets("出荷貼付け").Range("C1:C5"), "=0", Worksheets("出荷貼付け").Range("D1:D5"))

これは出荷貼付けのセルＣ１からＣ５がゼロに対するＤ１からＤ５の中の該当セルの値を集計するになります。

cocokuさんの
ActiveCell.FormulaR1C1 = "=SUMIF(出荷貼付け!C1,RC1,出荷貼付け!C5)"と多少違いますので、データの設定のせいでは？と思います。RC1がエラーのような。
無理やり解釈すると、Ｃ１セルがＲＣ１セルと同じなら、Ｃ５セルを集計せよです。

Range("O5").AutoFill Destination:=Range("O5:O978")
あとは、これでうめちゃうと同じ値になりませんか？
結果は値になるので、関数コピーのようにはなりませんから。

どうでしょう？
マクロなら、ＳＵＭＩＦにもよりますが、ＡｕｔｏＦＩｌｌじゃなくて、Ｏ９７８までmatsu_junさんの肝を順にセットしていかないとできないと思います。

お礼

ありがとうございます。
上記に記入して頂いた方たちも含めて感謝しています。
本で得られない勉強になりました。
一つ一つ自分もものにできる様に努力します。
これからもよろしくお願い致します。

回答No.2

matsu_jun

SUMIF文が正しくないような気がするのですが、これでよいのでしょうか？
もしSUMIF文の実際の値が

=SUMIF(出荷貼付け!C1:C5,"=0",出荷貼付け!D1:D5)

だとしたら、以下のようになります。

ActiveCell.FormulaR1C1 = Application.WorksheetFunction.SumIf(Worksheets("出荷貼付け").Range("C1:C5"), "=0", Worksheets("出荷貼付け").Range("D1:D5"))

キモは、VBA内でExcelの関数を利用するためのおまじない
　　Application.WorksheetFunction.
を利用することと、他のシートを参照する時に「(参照シート名)!」と書くところを
　　Worksheets("シート名").Range("セル範囲")
と書き換えることです。

なお、cocoku様のマクロを多少シェイプアップしました。コードをすっきりさせると共に、動作も高速化します。

Sub Macro4()
Application.ScreenUpdating = False '画面の書き換えを抑制します。
Columns("O:O").Insert Shift:=xlToRight
Range("N3").AutoFill Destination:=Range("N3:O3"), Type:=xlFillDefault
Range("O5").Value = Application.WorksheetFunction _
.SumIf(Worksheets("出荷貼付け").Range("C1:C5"), "=0", Worksheets("出荷貼付け").Range("D1:D5"))
Range("O5").AutoFill Destination:=Range("O5:O978")
Application.ScreenUpdating = True '画面の書き換えを許可します。
End Sub

よろしければご利用ください。

補足

ありがとう御座います。
キモの意味がよくわかりました。
しかしながら、うまく行きません。
matsu_junさんのすっきりとしたコードを
ハイシャクしましたが、数式は消えたのですが
値がすべて"0"になってしまいます。

"出荷貼付け"のsheetには
B列は　品番
C列は　カラー
D列は　サイズ
E列は　数量
が記入され
A列には、=CONCATENATE($B2,$C2,$D2)の品番・カラー・サイズが入力されています。

別のシートの
A列に　品番・カラー・サイズ
O列に行を挿入してsumif関数で数量の集計をしていたのですが。
=sumif(出荷貼付け!A:A,別のシート!$A1,出荷貼付け!E:E)
という物を作ったつもりなのです。
マクロの記録でなんとか、ダラダラマクロをでやって
きましたが、数式ばかりが入ってしまい
10シートぐらいになり重く見くいものとなってしまいました。
それでなんとかならいないものかとご相談に。
sumif関数が変なんでしょうか?

回答No.1

papayuka

計算させてから、コピーして値で貼り付けするとか。

Sub Test()
　Columns("O:O").Insert shift:=xlToRight
　Range("N3").Copy Destination:=Range("O3")
　Range("O5:O978").Value = "=SUMIF(出荷貼付け!C1,RC1,出荷貼付け!C5)"
　Range("O5:O978").Copy
　Range("O5:O978").PasteSpecial xlPasteValues
　Range("O4").Select
　Application.CutCopyMode = False
End Sub