VBAでExcelのセルに数式を設定する

2020年5月6日

VBAのサンプルアプリを使ってセルに式を設定する方法を説明します。
以下のサンプルアプリは黄色のセルに所定の式を設定するプログラムです。
E列には「Do Until ~ Loop」を使って直接式を設定し、17行目の合計行は式のコピー貼付けで式を設定しています。

セルに数式を設定するサンプルアプリの説明

「式の設定」ボタンをクリックすると、E列に「単価(円)」×「数量」の式が、17行目の合計行にSUM関数を使った合計を求める式が入ります。

Object.Formulaプロパティと
Object.FormulaR1C1プロパティの説明

 Object.Formula    A1形式で数式の設定/取得
 Object.FormulaR1C1  R1C1形式で数式の設定/取得

 A1形式は行を行番号、列をアルファベットで表す形式
  Range(“C1").Formula = “=A1+B1" 'A1形式で相対参照
  Range(“C1").Formula = “=$A$1+$B$1" 'A1形式で絶対参照

 R1C1形式はRが行、Cが列を表し、現在のアクティブセルからの相対指定でセル位置を表す形式
  Range(“C1").FormulaR1C1 = “=RC[-2]+RC[-1]" 'R1C1形式で相対参照
  Range(“C1").FormulaR1C1 = “=R1C1+R1C2" 'R1C1形式で絶対参照
 

A1形式とR1C1形式の説明

 セル番地の表記法には、大きく分けて二種類の形式があります。
 「A1形式」は列をアルファベット順に並べた形式で以下のようになります。
 最初のアルファベットが列の位置、続く数字が行の位置になります。

R1C1形式の「R」はRow(行)、 「C」はColumn(カラム)を表し、
「R」が先であることから行・列の順にセルの位置を記述します。

サンプルサプリのソースコード  コードの作成手順はこちら

Sub ボタン1_Click()

   Dim myLast As Long
   Dim i As Long


  'シート名を入れる変数の定義
   Dim wS As Worksheet
  '変数 wS に シート名「Sheet1」をセット
   Set wS = Worksheets("Sheet1")

  'データ最終行を取得
   myLast = wS.Cells(Rows.Count, 2).End(xlUp).Row  

'E列6行目~16行目の合計欄に式をセット
   i = 6
   '3列目のデータ(単価)があるところまでループさせる
   Do Until wS.Cells(i, 3) = ""
     'E列合計欄に式をセット
     wS.Cells(i, 5) = "=RC[-2]*RC[-1]"
     i = i + 1
   Loop
  
  
'17行目の合計欄に式をセット
  '17行目のC列に式をセット
   wS.Cells(myLast, 3) = "=SUM(C6:C16)"
  '上でセットした式をコピー
   Range(Cells(myLast, 3), Cells(myLast, 3)).Copy
  '17D,17Eセルに数式をペースト
   Range(Cells(myLast, 4), Cells(myLast, 5)).PasteSpecial Paste:=xlPasteFormulas
  'コピーモードを解除
   Application.CutCopyMode = False
   
   'E17のセルを選択して終了
   Range("E17").Select 
    
    
End Sub

A1形式からR1C1形式への変更方法

 Excel メニューの「ファイル」→ 「オプション」→ 「数式」を選択して、
 「R1C1参照形式を使用する(R)」にレ点マークを入れます。