VBA:校務マクロシートでのResizeの使い道

ずっと、Resizeの使い道がよく分かりませんでした。

Offsetは「ずらす」　Resizeは「広げる」　・・・意味は分かるんですが、

「Resizeってどんな時に使うんだろう？？？」・・・実感としてはイメージできなかったのです。

今回、授業時数予測シートを作ってみて、その威力を実感しました。

で、今回やりたかったことは、上の青枠の火曜日行（N10～S10）の時数時間割を、

下の青枠の火曜日行（E71～J71）に転記したかったのです。

つまり、ひとまとまりの範囲を、授業日の曜日ごとに転記したかったわけです。

できるだけシンプルにコードを書きたかったので、

１列だけ配列に入れて、Offsetで列方向にずらしてデータを転記するやりかたは思いつきました。

しかし、横に６列分のデータを一気に転記するにはどうしたらいいのか・・・

少し悩みました。

で、書いてみたコードが以下です。

-------------------------------------------

Sub 基本時数セット()

　Dim y, j, k As Range
　Dim myrng As Range

　　Set myrng = Range("N9:S13") '★週間基本時数

'★曜日列を配列格納

For Each y In Range("C9:C" & Cells(Rows.Count, 3).End(xlUp).Row)

　Set j = y.Offset(0, 8) '授業日列
　Set k = y.Offset(0, 2).Resize(1, 6) ' ★１～６年時数列１行分範囲設定
'-----------------------
　　If y.Value = "月" And j.Value = "●" Then
　　　k.Value = myrng.Value '１行目（月）代入
　　End If
'-----------------------
　　If y.Value = "火" And j.Value = "●" Then
　　　k.Value = myrng.Offset(1, 0).Value '２行目（火）代入
　　End If
Next

End Sub

-------------------------------------------

まず、赤文字コードで、曜日列の配列yから、.Offset(0,2)で、２列横にｋを作り、

ｋを、Resize(1,6)で１行６列分に広げて、書き込み先の範囲の大きさを確保しました。

次に、青文字コードで、火曜日は、myrngを１行ずらして、kに転記しました。

書き込み先の範囲 k の大きさをすでに確定しているので、縦にずらしていくだけで転記できます。

このように、配列で縦にセルを探し、Offsetで横にずらし、Resizeをかますことで

転記先のセル範囲の大きさを決めることができるので、データを一気に転記できます。

数式のVlookupに似た処理ができると思います。

数式だとセルを壊してしまう心配をしないといけませんが、

マクロだと、セルを何回でも書き替えられます。

学校現場では、このようなマクロの使い方はかなり有効だと思いました。

なお、太文字部分のコード

'★曜日列を配列格納

For Each y In Range("C9:C" & Cells(Rows.Count, 3).End(xlUp).Row)

は、うるう年対応です。

最終行が１増える場合があるので、最終セルを取得できるように、

Rangeを可変にしてみました。

For Eachを使うと、Rangeをそのまま配列に入れられるので処理が楽だし、速度も

For next　より速い感じがします。

ということで、今回言いたかったのは、

「配列をOffsetして、Resizeで書き込む範囲の大きさをあらかじめ決めておく」

と便利に使えるなあということでした。

備忘録も兼ねて書きました。

では、また(^^)/