和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > Range・Cells > セル範囲の数式をコピーする - Range.Formula

セル範囲の数式をコピーする - Range.Formula

Range・Cells 基本の書き方 シート関数・VBA関数 VBA

セルに入力された数式・関数を、
VBAを使って別のセル範囲へコピーする方法を紹介します。

セル範囲に数式を入力するコード

関数のコピーを行う表

D列に数式を入れましょう。
売上=価格×個数をやりたいので、「=B2*C2」が目的の数式ですね。


これをD2～D7セルに入力する場合は、以下のコードを実行します。

Range("D2:D7").Formula = "=B2*C2"

　

かなり簡単なコードで実行できますね。


「セル範囲.Formula = 数式」と入力した場合は、
セル範囲の第1セルにその数式を入力し、
その後他のセルへコピーしたときと同じように数式が入ります。


条件付き書式の数式をいじったことがある人は、
あれと同じような書き方というとわかりやすいでしょうか。


この「数式のコピー」について検索された方は、

  1. まずD2セルに数式を入れる
  2. D3～D7にその数式をコピーする

という手順をイメージされたかもしれません。


しかし、数式を一括入力するだけなら、

Range("D2:D7").Formula = "=B2*C2"

この1行で済むということになります。


簡単で便利な方法ですので覚えておきましょう。

あるセルの数式をコピーするコード

ただ数式を入力したい場合は、

  1. まずD2セルに数式を入れる
  2. D3～D7にその数式をコピーする

この手順は不要でした。


しかしD2セルの数式がシート上で既に入力されていて、
それをコピーしたいという場合もあると思います。


関数のコピーを行う表

このD2セルに既に数式が入っている場合に、
それをD3～D7セルへコピーする方法がこちらになります。

' D2セルの数式をD3:D7セルにコピーする
Range("D2:D7").Formula = Range("D2").Formula

　
この方法でD2セルの数式をD3～D7セルにコピーすることができます。


仕組みはとても単純で、セルの数式を取得するFormulaプロパティを使った、

Range("D2").Formula

このコードで「=B2*C2」を取得できます。


それを、

Range("D2:D7").Formula = Range("D2").Formula

これに代入してみると、

Range("D2:D7").Formula = "=B2*C2"

となって、目的の式になっていることがわかります。

！注意！コピーするセルもペースト範囲に含めること

この「数式をコピーするコード」には危険な罠があります。

手作業と明確に異なる点があったのですが、気づきましたでしょうか？
　

Range("D2:D7").Formula = Range("D2").Formula

↑よく見ると、
コピー先エリアがD2:D7と、コピー元であるD2セルが含まれていますね。


これがかなり重要なポイントです。


ただ「D2をD3～D7セルにコピー」というと、以下の式が思いつきます。

Range("D3:D7").Formula = Range("D2").Formula

D2をD3:D7へ、という素直な書き方です。


ですがこれをやると、
D3に「=B2*C2」が入力され1行ずつ関数がズレてしまうのです。


ここで冒頭の説明を思い出してください。

「セル範囲.Formula = 関数式」と入力した場合は、
セル範囲の第1セルにその関数を入力し、
その後他のセルへコピーしたときと同じように数式が入ります。
　

その仕様を意識してもう一度コードを見ましょう。

Range("D3:D7").Formula = Range("D2").Formula ' =B2*C2

　

よーくコードを見ると、素直に仕様通りに処理されただけなのがわかります。

手作業からくる錯覚で、このミスは気づきにくいですので注意してください。


数式のコピーを行う際は、「コピー元のセルをコピー先にも含める」必要があることを覚えておいてください。



なお、先ほどダメだった

Range("D3:D7").Formula = Range("D2").Formula

この書き方ですが、実は、

Range("D3:D7").FormulaR1C1 = Range("D2").FormulaR1C1

と、プロパティを「FormulaR1C1」に変えると成功します。


ただ、この方法はFormulaR1C1の正規の使い方ではなく、
裏技的な使い方なのでおすすめしません。

稀にこの解決策を取っているコードを見かけると思いますので、
コードを読むとき用に、心の片隅にでも置いておいてください。

おまけ：実はこの方法、超高速マクロの入り口です

この方法を探していた方は、
シートに入力する関数の作成も自動化したいとか、
関数の方が楽に書けるような処理をマクロでやりたいとか、
そういった需要と推測します。


が、この方法、簡単に書けるだけでなく、実は滅茶苦茶早いです。
　

For R = 2 To 7
    Cells(R, 4) = Cells(R, 2) * Cells(R, 3)
Next

こんな感じで普通にForを回すより速いのは当然なのですが、
　

' VLOOKUPをFormulaで
Range("D2:D7").Formula = "=VLOOKUP(A2,くだものリスト!A:B,2,FALSE)"

' VlookupをWorksheetFunctionで
For R = 2 To 7
    Cells(R, 4) = WorksheetFunction.Vlookup(Cells(R, 1), wsくだものリスト.Columns("A:B"), 2, False)
Next

↑このような、
「まったく同じ関数で戦う　Formula VS WorksheetFunction」の戦いでも、
Formulaが圧勝します。


理由を説明しますと、
1番大きいのが、「セルひとつひとつに入力していないから」です。


よく「配列にすると早い」と言われますよね。

あれは「配列でメモリ上で処理するから早い」というのもありますが、
「処理後に配列ごと一気にセルに戻している」のが、早さの一番の理由です。


配列に限らず、

' 100回かけて1を代入
For R = 1 To 100
   Cells(R, 1) = 1
Next

' 1回で1を代入
Range("A1:A100").Value = 1

↑のような単純な処理でも、後者が圧倒的に速いというのが、
配列を使った高速化のカラクリです。


今回のFormulaプロパティへの関数一括入力は、
すべてのセルに対して同時に入力されていますので、
配列でやったときに近い速度効果が得られるというわけですね。


この方法がチートなのは、
「配列などの高度な高速化に比べて、圧倒的にコーディング難易度が低い」ことです。


「わたしシート関数はわかりますが、VBAはそんなには…。」

くらいの人が、書けてしまいそうな難易度です。


そのうち検証記事を書くと思いますが、皆さんも使ってみてください。