VBA100本ノック 6本目：セルに計算式

公開日：2020-10-24　最終更新日：2021-08-25

VBA100本ノック 6本目：セルに計算式

セルに計算式を入れるVBAです。
どのような形式の計算式をどのプロパティに入れるか。

ツイッター連動企画です。
ツイート文字数制限があるので、ブック・シート指定等省略可能な記述は省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 6本目
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_06.xlsm
https://excel-ubara.com/vba100sample/VBA100_06.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

シート指定は省略します。


  
    
      Dim i As Long

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

　　If Not Cells(i, 1).Value Like "*-*" Then

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

　　End If

Next

最下行が非表示ではないことが前提になります。

Like演算子はInstr関数を使っても良いです。
If InStr(Cells(i, 1), "-") = 0 Then
Like演算子、Instr関数、R1C1参照については記事の補足にて。
また、Unionメソッドを使って一括で相対参照で入れるコードも掲載しておきました。

補足

この問題の場合、R1C1形式を使わないとかなり面倒な処理になります。
範囲全てに同一数式を入れてしまって良ければ、相対参照で一括で数式を入れられます。


  
    
      Dim rng As Range

Set rng = Range("A1").CurrentRegion

Set rng = Intersect(rng, rng.Offset(1, 3))

rng = "=B2*C2"

これは相対参照になっているので、実際に入るセルによって参照するセルが勝手にずれてくれます。
しかし、個別の行に数式をいれるとなると、その行からみた参照で数式を入れなければなりません。
このような場合は、R1C1で数式を作成すると、どの行にも同一の数式を入れることができます。
R1C1参照の数式は、FormulaやValueにも入れることができますが、可読性の観点からもFormulaR1C1プロパティを使った方が良いと思います。

また、Unionメソッドを使って対象セルをRangeオブジェクトに入れればA1形式の相対参照でも一括で入れられます。


  
    
      Dim i As Long, rng As Range

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

　　If InStr(Cells(i, 1).Value, "-") = 0 Then

　　　　If rng Is Nothing Then

　　　　　　Set rng = Cells(i, 4)

　　　　Else

　　　　　　Set rng = Union(rng, Cells(i, 4))

　　　　End If

　　End If

Next

If Not rng Is Nothing Then

　　rng.Formula = Replace("=B2*C2", 2, rng.Row)

End If

これはシートで飛び飛びのセルを選択して、数式を一括入力している操作になります。

Like演算子

文字列比較のための便利なパターン文字列式を、
ワイルドカード、文字リスト、文字範囲などを組み合わせて指定できます。

パターン文字列式（ワイルドカード、文字リスト、文字範囲）

文字パターン	引数stringの中の一致する文字
?	任意の1文字
*	任意の数の文字
#	任意の1文字の数字(0-9) 1バイト(半角)の数字半角全角どちらのの数字にも一致します
[charlist]	文字リストcharlistに指定した文字の中の任意の1文字
[!charlist]	文字リストcharlistに指定した文字以外の任意の1文字

1文字とは

2バイト文字(全角文字)も1文字と数えて文字列比較を行います。

特殊文字の指定

特殊文字の左角かっこ([)、疑問符(?)、数値記号(#)、およびアスタリスク(*)を文字列比較するには、
これらの文字を角かっこで囲み、[*]のように指定します。

文字リスト

ハイフン (-) を使用して範囲の上限と下限を分離することで、charlistで文字の範囲を指定できます。
たとえば、
[A-Z]では、string内の対応する文字位置にAからZの範囲内の大文字が含まれる場合に一致します。

Instr関数

文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。

InStr([start, ]string1, string2[, compare])

start	省略可能です。検索の開始位置を表す数式を指定します。省略すると、先頭の文字から検索されます。引数 start に Null 値が含まれている場合、エラーが発生します。引数 compare を指定した場合は、start も指定する必要があります。
string1	必ず指定します。検索対象となる文字列式を指定します。
string2	必ず指定します。引数 string1 内で検索する文字列式を指定します。
compare	省略可能です。文字列比較の比較モードを指定する番号を設定します。引数 compare が Null 値の場合は、エラーが発生します。引数 compare を指定した場合は、引数 start も指定する必要があります。引数 compare を省略すると、Option Compare ステートメントの設定に応じて、比較モードが決まります。ローカル固有の比較ルールを使用するには、有効なLCID (LocaleID) を指定します。

R1C1参照

セルの相対位置、つまり、計算式を設定しようとしているセルからの移動量を、
行位置をRに続けて記述し、列位置をCに続けて記述します。


  
    
      R ・・・ Rの後に何も記述しない場合は同一行

C ・・・ Cの後に何も記述しない場合は同一列

R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数

C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数

R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をずらした行

C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をずらした列

これらを組み合わせて、計算式を設定するセルからの位置を指定します。
例．C5セルに計算式を設定する場合、


  
    
      RC1 ・・・ A5セル
R1C ・・・ C1セル
R[1]C[2] ・・・ E6セル
RC[-1] ・・・ B5セル

サイト内関連ページ

第10回．Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

・Cells(行番号, 列番号) ・Rows(行番号) ・Columns(列番号) ・RangeとCellsの関連記事

第11回．RangeとCellsの使い方｜VBA入門

・RangeとCellsの基本的な使い分け方・固定セル（固定位置）の指定・Rangeに変数は使わないようにします・１つの（VBAで位置を変化させる）セルを指定する場合・セル範囲（複数セル）を指定する場合・複数行全体、複数列全体の指定・RangeとCellsの使い分け方のまとめ・RangeとCellsの基本の関連記事・RangeとCellsの応用の関連記事

第38回．セルに計算式を設定（Formula）｜VBA入門

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

第49回．Like演算子とワイルドカード

・Like演算子・パターン文字列式（ワイルドカード、文字リスト、文字範囲）・Like演算子の使用例・正規表現について

第47回．VBA関数（文字列操作,Replace,InStr,StrConv）

・文字列操作に関するVBA関数の一覧・Replace関数・InStr関数・StrConv関数・最後に

同じテーマ「VBA100本ノック」の記事

3本目：セルの消去

・出題・頂いた回答・解説・補足・サイト内関連ページ

4本目：セルの消去

・出題・頂いた回答・解説・補足・サイト内関連ページ

5本目：セルの計算

・出題・頂いた回答・解説・補足・サイト内関連ページ

6本目：セルに計算式
7本目：日付データの扱い

・出題・頂いた回答・解説・補足・サイト内関連ページ

8本目：点数の合否判定

・出題・頂いた回答・解説・補足・サイト内関連ページ

9本目：フィルターコピー

・出題・頂いた回答・解説・補足・サイト内関連ページ

10本目：行の削除

・出題・頂いた回答・解説・補足・サイト内関連ページ

11本目：セル結合の警告

・出題・頂いた回答・解説・補足・サイト内関連ページ

12本目：セル結合を解除

・出題・頂いた回答・解説・補足・サイト内関連ページ

13本目：文字列の部分フォント

・出題・頂いた回答・解説・補足・サイト内関連ページ

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
6本目：セルに計算式

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

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 6本目：セルに計算式