EXCEL VBA RangeオブジェクトOFFSETプロパティ（基準セルからの行・列移動・相対参照・セル範囲を移動する）
EXCEL VBA RangeオブジェクトOFFSETプロパティ（基準セルからの行・列移動・相対参照・セル範囲を移動する）
今回説明するのは、RangeオブジェクトのOffsetプロパティを説明いたします。Offsetプロパティは、基準となる指定したセル位置に対して、行方向・列方向の移動数を指定する事で、セルの参照位置・参照範囲を移動する事ができます。
Offsetプロパティは、セルの参照位置を何らかの計算結果で参照範囲を変更する事ができるので、覚えるととても便利な機能です。それでは、サンプルプログラムを交えて順番に説明いたします。
●【Resizeプロパティに関する内容は、下記の参照して下さい】
● Offsetプロパティを利用するには、下記の通りに設定を行います。
名前 データ型 既定値 説明（内容）
RowOffset Variant 0 標準セルに対して行方向を設定します。上方向は、－（マイナス）数値・下方向は、数値を指定します。
ColumnOffset Variant 0 標準セルに対して列方向を設定します。左方向は、－（マイナス）数値・右方向は、数値を指定します。
【使用例】
●下記の使用例は、セル（C3）を基準とするOffsetプロパティの使用例です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
' ' Sub OffSet00() 'OFFsetの使用例（サンプル） Range("C3").Offset(-2, -2).Select 'セル[A1]に移動 Range("C3").Offset(-1, -1).Select 'セル[B2]に移動 Range("C3").Offset(1, 1).Select 'セル[D4]に移動 Range("C3").Offset(2, 2).selec 'セル[E5]に移動 End Sub ' |
【注意点】
・Offsetプロパティを使う注意点として、Offset(1)やOffset(,1)の様に、移動方向０を省略する事ができますが、他人がプログラムを見た時に、分かりずらいので、省略せずに【例】Offset(1,0)・Offset(0,1)の通りに記述しましょう。
OffSetプロパティを利用して見出しを作成
下記のサンプルプログラムは、Offsetプロパティを利用して見出しを作成します。基準となるセル（B2）を元にOffsetで「社員番号」・「氏名」・「所属」・「役職」とOffsetで列方向を加算しながらセルに転記します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
' ''******** AKIRA55.COM ******* https://akira55.com/offset/ ' Sub Offset01() 'Offsetを利用して見出しの作成 With Range("B2") 'セル（B2）を基準とします。 .Value = "社員番号" 'B2に「社員番号」を代入 .Offset(0, 1).Value = "氏名" 'C2「氏名」を代入 .Offset(0, 2).Value = "所属" 'D2「所属」を代入 .Offset(0, 3).Value = "役職" 'E2「役職」を代入 End With Range("B2:E2").Interior.ColorIndex = 6 'Offsetプロパティで文字列を代入したセルに背景色を付けます。 End Sub ' |
（画面クリックして拡大）
Offsetを利用して複数の見出しの作成 (指定した位置に見出しを作成)
下記のサンプルプログラムは、Offsetを利用して指定した位置に見出しを作成するサンプルプログラムです。
【プログラム実行条件】
・A列の【見出し位置】A2～A５に見出し位置をセル番号で指定します。
・B列に【見出し】B２～B5に見出し名称を指定します。
・A列・B列ともに、最大４件まで
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
' ''******** AKIRA55.COM ******* https://akira55.com/offset/ ' Sub Offset02() 'Offsetを利用して複数の見出しの作成（同じ見出しの作成） Dim Ichi As Range Dim I As Long For Each Ichi In Range("A2:A5") '表示する見出し位置（セル番号）を順番に読み取る With Range(Ichi) 'セル位置をセットして基準とします。 For I = 0 To 3 'Offsetでの列移動および見出し名を順番に繰り返します。 .Offset(0, I).Value = Cells(I + 2, "B") 'Offsetを使って表題を転記します。 .Offset(0, I).Interior.ColorIndex = 8 '表題の背景色を塗りつぶします。 Next I End With Next Ichi End Sub ' |
（画面クリックして拡大）
Offsetを利用して一意のデータを作成してデータを転記します。
下記のサンプルプログラムは、Offsetを利用してEXCELシート上に、２つの混在するデータがあります。この混在するデータを一意のデータに纏めます。データを纏める際に、Offsetを利用してデータを転記します。
【プログラム説明】
①一つのワークシートに人事データAと人事データBがあります。
②この２つの人事データを人事データAを元に人事データBにあって人事データAに無いものを人事データAの最終行に追加します。
【プログラム実行条件】
・A列～D列の範囲に人事データAを作成
・F列～I列の廃位に人事データ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 |
' ''******** AKIRA55.COM ******* https://akira55.com/offset/ ' Sub offset03() 'Offsetを利用して一意のデータを作成する。（一つのデータに纏める） Dim Syain As Range Dim I, lRow As Long lRow = Cells(Rows.Count, "F").End(xlUp).Row 'F列の最終行を取得 For I = 2 To lRow 'F列の最終行（社員番号）まで繰り返す。 Set Syain = Range("A:A").Find(Range("F" & I)) 'A列の社員番号（全体）とF列の社員番号1件を重複するか確認します。 If Syain Is Nothing Then 'A列側の社員番号に登録されていないデータは、登録します。 With Cells(Rows.Count, "A").End(xlUp) 'A列の最終行をOffsetの基準としてセット .Offset(1, 0) = Cells(I, "F") 'F列の社員番号を転記 .Offset(1, 1) = Cells(I, "G") 'G列の氏名を転記 .Offset(1, 2) = Cells(I, "H") 'H列のメールアドレスを転記 .Offset(1, 3) = Cells(I, "I") 'I列の出身地を転記 End With End If Next I End Sub ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。