６本目「セル内文字の検出、相対参照による数式挿入」を学ぶ - エクセルマクロを０から独学で極める極意～ITを味方に～

今回は少し難しいです。そのため基礎知識の内容がヘビーです。しかし一つ一つゆっくり理解していけば、
問題ありません。基礎知識が蓄積されるのに伴い、もちろんマクロでできること、こういうマクロを組みたい！という自分で持てるオリジナルなマクロの構想も広がってくと思いますし、一緒に勉強頑張りましょう！

お題
基礎知識
回答
学んだこと総括

お題

f:id:Rimux:20210717092151p:plain excel-ubara.com

使用するファイルはこちら

f:id:Rimux:20210717102712p:plain

A列セルに「ー」があるとき：　A列に枝番「 - 」がついている行のD列には、何も入れない。
A列セルに「ー」がないとき：　A列に枝番がついていなければD列に、B列*C列の結果を入れる。
という処理内容のお題です。

基礎知識

Like 演算子

今回のお題では、A列セルの（＝文字列の間に「ハイフン」を含むならば、）という条件式を作る必要があります。そして、そのあとの処理を分岐させます。

指定文字を検出するためには、パターン文字列式を以下の表で覚えましょう。暗記です。

Like演算子は、String（指定した文字列）とパターン文字列式を比較します。
（半角文字は１バイト、全角文字は２バイトとなりますが、ここでは「全角文字（２バイト文字）も一文字として比較」します）

f:id:Rimux:20210717104543p:plain 　

今回のお題では、ハイフンの前後に複数文字ありますね。ここで上の表をみて、ならアスタリスクを使えばいいんだなと！と確認できます。たとえば、これがハイフン前後が一文字A列の表示が「B-2」みたいな感じであれば、上の表を参考にパターン文字列式は「?-?」と指定できますよね。要するに場面に応じた使い分けが必要です。
実際に使ってみましょう。

'A２セルの文字列の間に「ー」が入っていなければ(※前後複数文字)
If Not Cells(2, 1).Value Like "*-*" Then

このLike演算子で調べる方法を覚えるのが一つ。
そして同じことを、InStrという関数でも実現できます。

InStr関数は非常に重要なため、あえてこのパターンも見ていきましょう。

InStr関数

‘書き方 []内は省略可能
InStr([start位置,]検索範囲,検索文字列[,モード指定])

これは使用例を見た方が早いです。

InStr("macroDaisuki","a") 　⇨「２」
(startを省略しているため、1文字目から検査されます)

f:id:Rimux:20210717114825p:plain

スタート位置を指定する場合（これを使うことで２回目以降に現れる位置番号等を取得が可能）

InStr(3,"macroDaisuki","a") 　⇨「7」

f:id:Rimux:20210717114358p:plain

InStr(2,"macroDaisuki","a") 　⇨「2」 (スタート位置は含むことがわかります)

f:id:Rimux:20210717114424p:plain

検索した指定した文字列が対象から見つからなければ「０」が返されます。

先ほどLike演算子を用いて書いた「ハイフンを含まなければ」というコードをInStr関数で置き換える・・

'A２セルの文字列の間に「ー」が入っていなければ(※前後複数文字)
If InStr(Cells(2, 1),"-") = 0 Then

となります。両方使えるようにしておきましょう。前回までにCurrentRegionを使うやり方で最終行数を取得していました（以下の通り）。

f:id:Rimux:20210717180615p:plain

今回は別やり方で取得します

Cells(Rows.Count,1).End(xlUp).Rows.Count

これまた新しい呪文です。難解なものはまずエクセル操作として理解します。
まず、

① エクセルシートを開いて、A列の表の最後の行のセルを選択します

② 次にCtr+↓でエクセルシートの最下行を選択します。（すると、このような莫大な行数のセルが選択されます）

f:id:Rimux:20210717182303p:plain

今あなたが選択しているセルがシートの最終行　Cells(Rows.Count,1)です。
ではここから表の最終行に移動するためにはどうしたらよいでしょうか。

そうです！今度は逆にCtr+↑を押せばOKです。(MacならCommand+↑)

③ End(xlUp) (これが「Ctr+↑」の操作をコードに置き換えたものです）　
　実際に操作してみると、表の最終行が選択されていますね。

f:id:Rimux:20210717182200p:plain

あとはトドメです！そのセル（A13）の行数は何行目かを取得します。今までも何回も使ってきた
Rows.Countを使います。

最終的に　Cells(Rows.Count,1).End(xlUp).Rows.Count　となるわけです。
ぱっと見ごちゃついてて分かりにくいですが、処理ごとに３分割して考えると簡単でしたね！

プログラムにこう命令しています。

Cells(Rows.Count,1)　　A列のシート最下段をとってきて
.End(xlUp)　　そこから一気に上にのぼって、表下部に激突して
.Rows.Count　激突した行数を答えて
みたいな感じですかね笑＾＾

ただし、この方法の難点として、A列にどこか一つでも空白や文字などが入ってゴミのセルがあると、
そこで止まって、誤ってそのセルの行数を取得してしまいます。
なので、使うのであれば、A列に表の要素以外の無駄なものが入っていないことが前提条件です。

やはりこの欠点を考慮すると、最終行はEnd(xlUp)で下から測るより、表上余白＋CurrentRegionで表サイズ、つまり上から測ってったほうが無難じゃないの？
と感じました。ですが今後こなすお題の中で、End(xlUp)でしか最終行を取得できないケースがもしあったら、そのときは再考する余地はありですね。

最後にもう一つ大事な概念があります。

FormulaR1C1 （R1C1参照）

計算式を入れたいセルがあるとした際に、そのセルからみたときの相対的な位置を［］で指定することで、セルを参照します。
これも使用例から覚えた方が手っ取り早いです。RはRow（行）、CはColumn（列）を示します。［］をつければ相対指定、付けなければ絶対指定です。

例
計算式を入れるセル：D2
D2にはB2*C2　をいれたい、とします。
これを相対指定で書く場合
Range("D2").FormulasR1C1 = "=RC[-2]*RC[-1]"
（D2セルから2列左に戻るとB2、１列左に戻るとC2、といったように、「D2から見た相対的な位置」で示します）
例えば、この左辺のD2をD3に書き換えて見てください。すると、
D3 ＝B3＊C3
となりましたよね？右辺は何もいじっていないのに、です！

つまり、今回のお題で言えば左辺をCells(i,4）としてD列固定して、あとはFor分で行数 i を表行数までぶん回してしまえば、
右辺は　"=RC[-2]*RC[-1]" と固定しておくだけ、勝手に行が1行ずつズレてくれることになります。。なんとも便利な参照ですね。すばらしすぎる。

回答

基礎知識さえ覚えてしまえば、ここからの回答自体の内容はかなり軽いと思われます。
基礎にはじまり基礎に終わる、もはやスポーツのようですが、それ位、上記基礎知識にぎっしり内容が詰まってます。

それではまず、表の最終行数をCurrentRegionをつかって取得します。

Range("A1").CurrentRegion.Rows.Count

これでD列の「２行目から「Range("A1").CurrentRegion.Rows.Count」行目まで」計算式を入れていけば良いとわかったのでFor文でまわします＿。

Dim i  as Long

For i = 2 To Range("A1").CurrentRegion.Rows.Count

計算式

Next

このままではA列セルのハイフン有無に関わらず計算式が全てに代入されてしまうので、ハイフンが無いときだけという条件を加えます。

'InStr 関数は指定文字列が対象になければ0を返す
if InStr(Cells(i,1),"-")=0 then

計算式

Endif

あとはFormulaR1C1でD列に計算式を代入する処理を書いて動かしてみます

Dim i  as Long

For i = 2 To Range("A1").CurrentRegion.Rows.Count

　if InStr(Cells(i,1),"-")=0 then

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

　Endif

Next