VBA練習問題
VBA100本ノック 5本目:セルの計算

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

VBA100本ノック 5本目:セルの計算


セルの消去に関する問題です。
セルを使った計算結果の値をセルに入れます。
また、表示形式も設定します。


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


出題

出題ツイートへのリンク

#VBA100本ノック 5本目
画像のようにB2から始まる表があります。
B列×C列を計算した値をD列に入れ、通貨\のカンマ編集で表示してください。
ただしB列またはC列が空欄の場合は空欄表示にしてください。
例.D2にはB3×C3の計算結果の値を「\234,099」で表示、D5は空欄
※ブック・シートは任意

VBA マクロ 100本ノック


頂いた回答

解説

シート及びValueを省略。
Dim i As Long
For i = 3 To Range("B2").CurrentRegion.Rows.Count + 1
  If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
    Cells(i, 4) = ""
  Else
    Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
  End If
Next
Columns("D").NumberFormatLocal = "\#,##0"

\の設定はNumberFormatLocalを使います。

上記コードの場合、
B列C列ともに空欄(つまり空行)があるような場合は、空行より下は処理されません。
End(xlUp)を使う場合、非表示行が無いことが前提となります。
UsedRangeを使う場合は、他の列にゴミデータがあると行数が大きくなってしまいます。
これらについては、記事の補足で。


補足

上記のVBAでは表示形式を列全体で指定しています。
表範囲内だけにしたいと思うかもしれませんが、エクセルのシートの列は同一書式で使う事が基本です。
とはいえ、範囲を限定したい場合の紹介も兼ねて、少し違ったVBAの書き方をしてみます。

Dim rng As Range
Set rng = Range("B2").CurrentRegion
Set rng = Intersect(rng, rng.Offset(1, 2))
rng.ClearContents
rng.NumberFormatLocal = "\#,##0"
Dim i As Long
For i = rng.Row To rng.Row + rng.Rows.Count - 1
  If Cells(i, 2) <> "" And Cells(i, 3) <> "" Then
    Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
  End If
Next

また、エクセルの計算では空欄は0と同じなので、構わずに計算してしまい、表示形式で0を空白表示するという方法も、やり方としては良いと思います。
さらに、セルに計算式を入れてから値貼り付けするという方法もありますが、これについては今後の題材に残します。


最終行の取得方法は各種ありますが、一長一短なところもあります。
扱う表形式を確認して適宜良い方法を選択するようにしてください。
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)|VBA技術解説
エクセルの表をVBAで扱う時は、データ部分の先頭から最終行までの、開始列から最終列まで処理する事が多いでしょう。開始行や開始列は、ほとんどの場合、見出し行や見出し列の次からになります。単純な話として、1行目に見出しがあれば、2行目から 1列目に見出しがあれば、2列目から では、ここで、最終行や最終列は、

先のRange("B2").CurrentRegion以外で、
今回の出題に対応した最終行取得のパターンをいくつか掲載しておきます。

With ActiveSheet
  Debug.Print .UsedRange.Item(.UsedRange.Count).Row
  Debug.Print .Cells.SpecialCells(xlCellTypeLastCell).Row
  Debug.Print WorksheetFunction.Max(.Cells(.Rows.Count, 2).End(xlUp).Row, .Cells(.Rows.Count, 3).End(xlUp).Row)
  Debug.Print Range("B:C").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

表範囲にフィターがかかっているとか、表の外側がどうなっているかによって一長一短があります。


サイト内関連ページ

第16回.繰り返し処理(For Next)|VBA入門
VBAのForNextは、同じ処理を繰り返し行うためのVBA構文です。繰り返し処理はループ処理とも呼ばれます。マクロでのループ処理の記述は何通りかありますが、まず最初に覚えるべきものが、今回説明するForNextです。
第17回.繰り返し処理(Do Loop)|VBA入門
繰り返し処理として、前回はFor~Nextをやりました、今回はDo~Loopです。For~Nextに比べると使用頻度は落ちますが、必ず覚える必要があるものです。For~Nextは、繰り返す回数をあらかじめ指定するものでしたが、Do~Loopは、繰り返す回数ではなく、繰り返す条件を指定するものです。
第18回.最終行の取得(End,Rows.Count)|VBA入門
Excelワークシートにおける表の最終行の取得は、VBAの必須技術になります、エクセルVBAにおける最終行取得の必要性 エクセルは表計算ソフトです、つまり縦横の表を扱います、データは横に項目があり、縦に項目に対するデータが入っている事が一般的です。しかし、そのデータ行数は決まった行数ではない事が普通です。
第31回.セルの書式(表示形式,NumberFormatLocal)|VBA入門
セルは表示形式を指定することで、セルに入っている値は同じ値でも色々な見え方をさせる事が出来ます。セルの値はそのままで、見た目だけをユーザーに見やすい形にすることが出来ます。同じ数値の、12345でも 12345、12,345、\12,345 等々の違う表示方法を指定する事が出来ます。




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

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


新着記事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」をお願いいたします。
本文下部へ