隣のセルはOffsetプロパティで参照できる
Offsetプロパティを使うと、基準となるセルから見て縦と横にどれだけ離れているか指定することで、隣のセルや離れているセルを参照することが出来ます。
これを相対参照と言います。
相対参照については「VBAで基準セルから相対セルを参照する（Offset）」をご参照ください。
相対参照はRangeオブジェクトのOffsetプロパティを利用します。Offsetプロパティの第一引数が縦にどれだけ移動するかを指定し、第二引数が横にどれだけ移動するかを指定します。縦にプラス値の場合は下に移動し、マイナス値の場合は上に移動します。横にプラス値の場合は右に移動し、マイナス値の場合は左に移動します。
考え方は以下のようになります。
例えば基準セルがE10セルであれば、右隣のF10セルは「Range(“E10”).Offset(0, 1)」で表現できます。
また、アクティブセルの左隣のセルは「ActiveCell.Offset(0, -1)」で表現できます。
Offsetプロパティで表現したセルもRangeオブジェクトになるため、ValueプロパティなどのRangeオブジェクトの各種プロパティを利用できます。
このように基準となるセルのRangeオブジェクトのOffsetプロパティを使うことで、隣のセルを参照することが可能になります。
Offsetプロパティでの注意点
Offsetプロパティは行と列を指定するため、意図するしないに関わらず、存在しないセルを参照するようなコードを書くことが出来ます。
例えば、A1セルはシート上で一番左上のセルですが、Offsetプロパティを使って、
|
1 |
Range("A1").Offset(-1, 0) |
のようにA1セルのさらに上の存在しないセルを参照するようなコードが書けますが、実行すると当然そんなセルは存在しないためエラーになります。
エラーを回避するには「On Error Resume Next」などでスルーするか、事前に実在するセルの範囲を取得しておくなどの考慮が必要になります。
|
1 2 3 4 5 6 |
Sub OffsetTest() On Error Resume Next Dim r As Range Set r = Range("A1").Offset(-1, 0) End Sub |
右隣のセルに処理結果を出力するサンプルコード
以下のコードは基準となるB列の日付を見て、C列に曜日を出力します。
曜日が土曜または日曜日の場合はB列の背景色を設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Sub OffsetWeekTest() Dim i Dim s Dim r As Range Dim sWeek '// 基準セルを設定 Set r = Range("B3") '// 基準セルから下に向かってループ Do '// セルの値を取得 s = r.Offset(i, 0).Value '// セルの値が未設定の場合はループを抜ける If s = "" Then Exit Do End If '// 書式を曜日に設定 r.Offset(i, 1).NumberFormatLocal = "aaa" '// 隣のセルに値を設定 r.Offset(i, 1).Value = s '// 曜日文字列を取得 sWeek = r.Offset(i, 1).Text '// 土曜日の場合 If sWeek = "土" Then '// 背景色を水色 r.Offset(i, 1).Interior.Color = RGB(204, 255, 255) '// 日曜日の場合 ElseIf sWeek = "日" Then '// 背景色をピンク色 r.Offset(i, 1).Interior.Color = RGB(255, 204, 255) End If i = i + 1 Loop End Sub |
実行結果
コード説明
基準となるB列から見て、曜日を出力するC列をOffsetプロパティで参照しています。
Offsetプロパティで表現されたセルもRangeオブジェクトですので、値の取得や参照、背景色の設定などが可能です。