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

2020年4月7日2021年12月25日

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

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

「式の設定」ボタンをクリックすると、Ｅ列に「単価（円）」×「数量」の式が、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