シートに数式を設定する時のセル参照の指定方法

公開日：2014-11-21　最終更新日：2014-11-21

シートに数式を設定する時のセル参照の指定方法

シートに計算結果ではなく、計算式を設定する場合の、セル参照の記述方法について解説します、
マクロVBAでは多くの場合、計算結果をセルに入れる事が多いのですが、時に計算式を設定する必要があります、
その時の、セル参照の記述が以外に面倒なものです。

下の表で説明していきます。
D列の残高(D3～D12)の計算式(E列に表示してある計算式)を設定してみましょう。

まず、非常にベーシックな方法として、R1C1形式での設定があります。
これは、マクロの自動記録を使う人にはお馴染みのものでしょう。


  
    
      Sub sample1()

　　Dim i As Long

　　For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row

      　　　　Cells(i, 4).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

　　Next

End Sub

FormulaR1C1またはFormulaR1C1Localについての詳しい解説は、
マクロVBA入門：第38回.セルに計算式を設定

・計算式を設定できるプロパティ・Valueプロパティ・Formulaプロパティ , FormulaLocalプロパティ・FormulaR1C1プロパティ , FormulaR1C1Localプロパティ・R1C1参照形式・Localが付くプロパティについて・それぞれの違い（Localは除く）・何故、こんなに多くのプロパティが存在しているのか・R1C1形式を使うメリット・たった1行のVBAで複数のセルに計算式を入れる

こちらを参照してください。

しかし、R1C1はちょっと分かりづらいのでしょうか、
以下のような苦心作を見かけることも良くあります。


  
    
      Sub sample2()

　　Dim i As Long

　　For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row

　　　　Cells(i, 4).Value = "=D" & i - 1 & "+B" & i & "-C" & i

　　Next

End Sub

もちろん、これはこれで問題はありません。
文字列結合を多用しているので、行数が多いと若干遅いかもしれないという程度です。

しかし、この題材の場合は、そもそも1行ずつ設定する必要がありません。
D3に数式を入れたら、オートフィルまたはコピペで済みます。
まず
オートフィルなら


  
    
      Sub sample3()

　　Dim i As Long

　　i = Cells(Rows.Count, 1).End(xlUp).Row

　　Range("D3").FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

　　Range("D3").AutoFill Destination:=Range(Cells(3, 4), Cells(i, 4))

End Sub

コピペなら


  
    
      Sub sample4()

　　Dim i As Long

　　i = Cells(Rows.Count, 1).End(xlUp).Row

　　Range("D3").Value = "=D2+B3-C3"

　　Range("D3").Copy Destination:=Range(Cells(4, 4), Cells(i, 4))

End Sub

少し書き方を変えてはいますが、マクロの自動記録を参考にして書くと、
大抵は、こんな感じになっているのではないかと想像して書いてみました。

しかしですね、そもそもオートフィルもコピペも必要がないのです。
ワークシートに数式を入れるとき、絶対参照・相対参照さえしっかり指定していれば、
対象セルを一括で選択しておき、Ctrl+Enterで一括入力ができることを知っていれば、
こんな簡単な記述で済んでしまいます。


  
    
      Sub sample5()

　　Dim i As Long

　　i = Cells(Rows.Count, 1).End(xlUp).Row

　　Range(Cells(3, 4), Cells(i, 4)).Value = "=R[-1]C+RC[-2]-RC[-1]"

End Sub

さらに、この方法ならR1C1形式を使うまでもなく、普通にセルに入れるように書けてしまいます。


  
    
      Sub sample6()

　　Dim i As Long

　　i = Cells(Rows.Count, 1).End(xlUp).Row

　　Range(Cells(3, 4), Cells(i, 4)).Value = "=D2+B3-C3"

End Sub

どう考えても、最後のVBAコードが一番わかり易いことは言うまでもないですね。
マクロはVBAの知識だけでなく、ワークシートの操作も同時に理解していることで、とても完結なコードが書けるようになります。

同じテーマ「マクロVBA技術解説」の記事

空白セルを正しく判定する方法2
Rangeオブジェクト.Valueの省略について
 シート保護でユーザー操作を制限する
シートに数式を設定する時のセル参照の指定方法
標準モジュールとシートモジュールの違い
 オートフィルタ（AutoFilter）の使い方まとめ
 複雑な条件（複数除外等）のオートフィルター（AutoFilter）
クリップボードを使わないセルのCopy
Rangeの使い方：最終行まで選択を例に
 フルパスをディレクトリ、ファイル名、拡張子に分ける
 Colorプロパティの設定値一覧（カラー定数、XlRgbColor列挙）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
シートに数式を設定する時のセル参照の指定方法

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA技術解説
シートに数式を設定する時のセル参照の指定方法