休日をまたいだ計算
今回は For文や If文を使って、実用的な内容にしてみたいと思います。
以下のような月末との差分、前日との差分を計算するようなマクロを仕込みます。
D列、E列には数式を設置します。
また、A列には、「データの入力規則」で「休」を選べるようになっています。
少々複雑なのは、休みの時は飛ばしてその前日の値と計算するという点です。
休みは2日以上で連続する事もありますので、それにも対応させます。
「データの入力規則」を設定する
まずはシート上の設定です。
A4～A34を選択して、「データ」＞「データツール」＞「データの入力規則」を選択します。
ダイアログが出ますので、以下のように設定します。
「元の値(S)」の所は、「休, ,」 としていますが、休の次に全角の空白を入れています。
こうする事で空白も選択リストに入るようになります。
（ただし、全角空白が入るだけで、文字が無い状態になるわけではないので注意してください。）
マクロの記録でコードを書いてもらう
今回は、セルの網掛けの処理を書いてもらいましょう。
適当なセルを選択した状態から、マクロの記録を開始します。
右クリックで「セルの書式設定」を選択して、「塗りつぶし」タブから以下のように「左下がり斜線 縞」を選択します。
OKを押して、これでマクロの記録を終了します。
プログラムは以下のようになったかと思います。
（多少違っていても抜き出して使いますのでそのまま先に進みましょう。）
Sub Macro1()
'
' Macro1 Macro
'
'/// 選択セルに網目をかけるマクロ
With Selection.Interior
.Pattern = xlUp
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub 今度は、網目をかけたセルを元の状態に戻すマクロを書かせてみます。
これもプログラムに必要です。
縞模様のセルを選択した状態でマクロの記録を開始します。
網目を消すので白の「塗りつぶし」を選択してOKを押し、網目を消した状態でマクロの記録を終了します。
（「塗りつぶしなし」が本来の目的ですが、選択できないのでここはこのまま進めます。）
Sub Macro2()
'
' Macro2 Macro
'
'///選択セルの網目を解除するマクロ
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Subプログラムの解説
今回のプログラムでは、休みを飛ばして処理する部分が厄介です。
（それと同時に手作業で行う煩雑さから解放されるのですが、、、）
まずはプログラムコードを示してから解説します。
Sub myMacro()
Dim i As Long
Dim j As Long
For i = 4 To 34
If Cells(i, "A") = "休" Then
'選択セルに網目をかけるマクロ
Cells(i, "D").Interior.Pattern = xlUp
Cells(i, "E").Interior.Pattern = xlUp
Cells(i, "D") = ""
Cells(i, "E") = ""
Else
'選択セルの網目を解除するマクロ
Cells(i, "D").Interior.Pattern = xlSolid
Cells(i, "E").Interior.Pattern = xlSolid
'数式をセット
Cells(i, "D").FormulaR1C1 = "=if(RC3="""","""",RC3-R3C3)"
For j = 1 To 5 '連休間隔
If Cells(i - j, "A") = "" Then 'j行分だけ前のA列が空白
Cells(i, "E").FormulaR1C1 = "=IF(RC3="""","""",RC3-R[-" & j & "]C3)"
Exit For
End If
Next j
End If
Next i
End Sub全体の行のループ
全体としては、4行目から34行目までをループさせて各行の処理を行います。
一行ごとにどのような処理を行うかは If文で判断します。
A列が “休”かどうか
最初のIf Else部分では、A列を見て “休”がどうかで処理を分けます。
”休”の場合は、網目をかける処理。
それ以外は、網目をかけない処理を施します。
マクロの記録を活用する
さて、前述の2つのマクロの記録ですが、ほとんど重複しているのに気が付くと思います。
この重複部分に関しては不要になるので、必要なのは Patternプロパティのみという事になります。
SelectionはRangeオブジェクトに置き換えられます。
今回は更にCellsに変更しています。これは ループする際に変数をそのまま使えるからです。
このように殆どの場合、RangeはCellsに置き換える事ができます。
あとは、.Interior.Patternを xlUpにすれば縞模様。 xlSolidにすれば無地になります。
With Selection.Interior ’Selection → Cells
.Pattern = xlUp ’xlUp=縞模様 xlSolid=無地
.PatternColorIndex = xlAutomatic ' 不要
.ColorIndex = xlAutomatic ' 不要
.TintAndShade = 0 ' 不要
.PatternTintAndShade = 0 ' 不要
End With
結果的に以下のようになります。
Cells(i, "D").Interior.Pattern = xlUp数式の入力工程
”休“の行では、値をクリアさせます。
見落としがちですが、必ず「以前の値が残っているかもしれない」という事を意識しましょう。
”休”の無い行では、数式を入れます。
まずはD列です。D列は黄色セルの月末の値との差を取れば良いので、
Cells(i, "D").FormulaR1C1 = "=if(RC3="""","""",RC3-R3C3)" のように書けます。
FormulaR1C1を使ってセルの位置を相対的に指定できるようにしています。
” （ダブルクォーテーション）がたくさん並んでいるのが気になりますが、これはエスケープシーケンスですね。
数式マクロは、もし同じ行のC列が空白なら、空白。それ以外なら、同行C列の値 - C3 の値を表示させます。
（個々の処理は休日が何度続いても関係なく処理できるので簡単ですね。）
次の前営業日との差ですが、For文を用いて連続した”休”を辿る必要があります。
今回は、5連休以上は無いという事を想定していますが、それ以上の連休が発生しそうな場合はもっと大きな数値に変えてもOKです。
For j = 1 To 5 '連休間隔
If Cells(i - j, "A") = "" Then 'j行分だけ前のA列が空白
Cells(i, "E").FormulaR1C1 = "=IF(RC3="""","""",RC3-R[-" & j & "]C3)"
Exit For
End If
Next j
内部の If文では、 i – j として前の行を辿っています。もしその行が空白であれば、その行の値が計算する相手になるわけです。
また、値を代入し終えたあとの、 Exit Forを忘れないようにしましょう。Exit Forは、 今ループしているFor文から抜ける処理です。これが無いと、その先までどんどん辿ってしまいますので、注意してください。
まとめ
最後にこのプログラムで注目しておきたいポイントを整理しておきます。
・マクロの記録の共通部分は不要
網目の設定・解除のマクロで共通する部分は不要になります。
・マクロの記録のSelectionはRangeに置換可能
マクロの記録ではSelectionがよく出てきますが、これらはRangeに置換可能です。
更にRangeはCellsに置き換えられますので、ループの内部ではCellsが便利です。
・何も入力しない行には、空白設定が必要
必要な部分に必要な値を入力するのは当然ですが、対象外となる部分に空白を設定することも忘れないようにしましょう。
そこには以前の値が入っている可能性があります。
・If文では色々なケースを想定しよう
今回は”休”か、そうでないかで条件分岐しました。
A列には、他の文字などが書かれてしまう場合もありますので、そのようなケースも想定する必要があります。
・For文の中のIf文では、Exit Forを適切に使おう
Exit Forを書かなくてもきちんとした結果が得られるケースがあるのですが、そのような場合でも不要なFor文の繰り返しは起こっているので適切とは言えません。
Exit Forを忘れないようにしましょう。
今回は、VBA初心者の方には少々難しく感じられたかもしれません。特にForループが二重になるようなケースでは頭が混乱するでしょう。
最初のうちは、動かしてみて不具合がでたら変更して、を繰り返しながらプログラムしていく形になるかと思います。試行錯誤を繰り返しているうちにプログラム脳が鍛えられるのだと思います。
