ワークシートの計算式を動的にセットするサンプルです。
(画像をクリックすると、このサンプルがダウンロードできます)
何行入力されるかわからないような表には、予め想定される最大の行数分に横合計などの計算式をセットしておくようなことをされていると思いますが、このサンプルでは、「数量」「単価」が入力されるとその行に計算式がセットされるようにしています。ですから空の状態では何も式がないので小さいファイルサイズで済みます。
このマクロは、ワークシート上でセル内容が変わったことのイベントで動作させます。このため、「Worksheet_Change」のイベント処理(Sheet1)に書き込みます。)を使います。
'***************************************************************************************************
' 計算式をVBAから書き込むサンプル Sheet1(Class)
'
' 作成者:井上治 URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev 変更履歴内容------------------------------------------------------------------------>
'03/07/11(1.00)新規作成
'04/02/28(1.01)初回修正
'20/02/23(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'***************************************************************************************************
' ■■■ ワークシートイベント ■■■
'***************************************************************************************************
'* 処理名 :Worksheet_Change
'* 機能 :セル値変更イベント
'---------------------------------------------------------------------------------------------------
'* 返り値 :(なし)
'* 引数 :Arg1 = Target(Range)
'---------------------------------------------------------------------------------------------------
'* 作成日 :2003年07月11日
'* 作成者 :井上 治
'* 更新日 :2020年02月23日
'* 更新者 :井上 治
'* 機能説明:数量、単価を入力した行に計算式をセットする
'* 注意事項:
'***************************************************************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------------------------------------
Dim lngRow As Long ' 行INDEX
Dim lngCol As Long ' カラムINDEX
' 変更した行を取得
lngRow = Target.Row
' 見出し行は無視
If lngRow <= 1 Then Exit Sub
' 変更したカラムを取得
lngCol = Target.Column
' 数量、単価以外は無視(商品列は無視)
If ((lngCol < 3) Or (lngCol > 4)) Then Exit Sub
' 計算式セット自体でもイベントが発生するのでイベントを抑制
Application.EnableEvents = False
' 計算式をセット(R1C1形式なら式は固定で良い)
Cells(lngRow, 1).FormulaR1C1 = "=ROW()-1" ' №(行番号-1)
Cells(lngRow, 5).FormulaR1C1 = "=RC3*RC4" ' 金額(=数量×単価)
' イベントを再開
Application.EnableEvents = True
End Sub
'----------------------------------------<< End of Source >>----------------------------------------