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

VBAを100本の練習問題で鍛えます
最終更新日:2021-08-25

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


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


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

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。


出題

出題ツイートへのリンク

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

VBA マクロ 100本ノック


サンプルファイルです。
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に変数は使わないようにします ・1つの(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本目:文字列の部分フォント


新着記事NEW ・・・新着記事一覧を見る

構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)
LAMBDA以降の新関数の使用例|エクセル入門(2022-08-22)
数珠順列(配置に条件付き)を全て出力する|ツイッター出題回答 (2022-08-20)
日付時刻のマイナス表示に対応する方法|ツイッター出題回答 (2022-08-17)
LAMBDA以降の新関数について|エクセル入門(2022-08-16)
条件付きの最大値と中央値("A"が2文字の条件)|ツイッター出題回答 (2022-08-14)
VBAマクロと操作対象データの分離について|ツイッター出題回答 (2022-08-11)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




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


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



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