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

VBAを100本の練習問題で鍛えます
最終更新日:2020-10-26

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


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


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


出題

出題ツイートへのリンク

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

VBA マクロ 100本ノック


頂いた回答

解説

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

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入門
Rangeの指定で、あらゆるセルおよびセル範囲は指定できるのですが、マクロで使う場合は、ちょっと使いづらい場合があります。"A1"や"B5"と言うような文字で指定するのでは何かと不便です、もっと、プログラムっぽい(笑)指定方法があります。
第11回.RangeとCellsの使い方|VBA入門
VBAではセルを指定する方法としてRangeとCellsがあります、RangeもCellsも、どちらもRangeオブジェクトでセルを指定するものです。どちらを使ったらよいのでしょうか、どう使い分けたらよいのでしょうか、実際のVBA記述では、RangeとCellsを使い分ける必要があります、RangeとCellsの使い方・使い分け方について解説をします。
第38回.セルに計算式を設定(Formula)|VBA入門
セルに計算式(関数)を設定する場合のマクロVBAになります。マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、決して使わないわけではありません。しかし、この計算式の設定には何種類ものプロパティがあり、結構やっかいなのです。
第49回.Like演算子とワイルドカード
文字列比較において部分一致やパターンマッチングでの文字列比較を行う時に使うのがLike演算子です。Like演算子は、2つの文字列のパターンマッチングを行い、規則に一致しているかどうかの結果をTrue(一致)またはFalse(不一致)で返します。
第47回.VBA関数(文字列操作,Replace,InStr,StrConv)
文字列操作は、マクロVBAでプログラミングする上で必須です、データ整形、データクレンジング、データクリーニング、これらを行うVBAにおいて不可欠なものが、文字列操作関数です。ここでは、文字列操作に関するVBA関数の一覧と Replace関数、InStr関数、StrConv関数について解説します。




同じテーマ「Python入門」の記事

VBA100本ノック 3本目:セルの消去
VBA100本ノック 4本目:セルの消去
VBA100本ノック 5本目:セルの計算
VBA100本ノック 6本目:セルに計算式
VBA100本ノック 7本目:日付データの扱い
VBA100本ノック 8本目:点数の合否判定
VBA100本ノック 9本目:フィルターコピー
VBA100本ノック 10本目:行の削除
VBA100本ノック 11本目:セル結合の警告
VBA100本ノック 12本目:セル結合を解除
VBA100本ノック 13本目:文字列の部分フォント


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

VBA100本ノック 39本目:数値リストの統合(マージ)|VBA練習問題(12月5日)
VBA100本ノック 魔球編:組み合わせ問題|VBA練習問題(12月3日)
VBA100本ノック 38本目:1シートを複数シートに振り分け|VBA練習問題(12月3日)
VBA100本ノック 37本目:グラフの色設定|VBA練習問題(12月2日)
VBA100本ノック 36本目:列の並べ替え|VBA練習問題(12月1日)
VBA100本ノック 35本目:条件付き書式|VBA練習問題(11月29日)
VBA100本ノック 34本目:配列の左右回転|VBA練習問題(11月28日)
VBA100本ノック 33本目:マクロ記録の改修|VBA練習問題(11月26日)
VBA100本ノック 32本目:Excel終了とテキストファイル出力|VBA練習問題(11月25日)
VBA100本ノック 31本目:入力規則|VBA練習問題(11月24日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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