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

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

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


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


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

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


出題

出題ツイートへのリンク

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

VBA マクロ 100本ノック


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


頂いた回答

解説

シート及び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 等々の違う表示方法を指定する事が出来ます。




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

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


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

キーボード操作だけで非表示列を表示|エクセル雑感(2021-05-11)
変数を考えることはロジックを考える事|エクセル雑感(2021-04-11)
RangeオブジェクトのFor EachとAreasについて|VBA技術解説(2021-04-08)
PropertyのSetはLetでも良い|VBA技術解説(2021-03-31)
エクセル麻雀ミニゲーム|VBAサンプル集(2021-03-09)
VBA100本ノック 100本目:WEBから100本ノックのリストを取得|VBA練習問題(2021-03-03)
VBA100本ノック 魔球編:2桁の最小公倍数|VBA練習問題(2021-02-02)
Select Caseでの短絡評価(ショートサーキット)の使い方|VBA技術解説(2021-01-03)
VBA100本ノック 迷宮編:巡回セル問題|VBA練習問題(2020-12-31)
VBA100本ノック 魔球編:閉領域の塗り潰し|VBA練習問題(2020-12-16)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.Excelショートカットキー一覧|Excelリファレンス
3.RangeとCellsの使い方|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
9.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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