Sub TEST1()
'VLookup関数で、別シートを参照する
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.VLookup("B", Worksheets("Sheet2").Range("A2:B15"), 2, False)
End Sub
ちょっと長いので、「With」でくくっておきます。
「With」でくくる
Sub TEST2()
'「With」でくくる
With Worksheets("Sheet2")
'VLookup関数で別シートを参照
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.VLookup("B", .Range("A2:B15"), 2, False)
End With
End Sub
Withでくくった方が見やすいですね。
では、別シートを用意しておきます。
別シートを用意
VBAコードを実行してみます。
別シートを参照して検索
別シートを参照して検索できました。
こんな感じで、別シートを参照する場合は「Worksheets」を使います。
シート番号を使って別シートを参照する
「Worksheets」に「シート番号」入力することで、「別シート」を参照することもできます。
シート番号は、先頭のシートから連番で付けられています。
「シート番号」のイメージ
「シート番号」を使って別シートを参照してみます。
「2番目のシート」の「Sheet2」を参照したいので「Worksheets(2)」とします。
「シート番号」を使って別シートを参照
Sub TEST3()
'シート番号を使う
With Worksheets(2)
'VLookup関数で別シートを参照
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.VLookup("B", .Range("A2:B15"), 2, False)
End With
End Sub
では、実行してみます。
「シート番号」を使って別シートを参照できました。
全シートをループして参照する
「全シート」をループして参照してみます。
先ほどとの「シート番号」を変数にするとできます。
こんな感じです。
全シートをループして参照
Sub TEST4()
'ループして使う
For i = 2 To 5
With Worksheets(i)
'VLookup関数で別シートを参照
Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.VLookup("B", .Range("A2:B15"), 2, False)
End With
Next
End Sub
集計用のシートでSheet1を用意しておきます。
「Sheet2～Sheet5」を用意しておきます。
では、VBAコードを実行してみます。
「全シート」をループして「VLookup関数」を使うことができました。
「シートの数」を取得してループ
「シートの数」を取得してループしてみます。
実務では、シートの数は変わることが多いです。
なので、「シートの数」を「Worksheets.Count」で取得すると便利です。
Sub TEST5()
'「最終シート」までループ
For i = 2 To Worksheets.Count
With Worksheets(i)
'VLookup関数で別シートを参照
Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.VLookup("B", .Range("A2:B15"), 2, False)
End With
Next
End Sub
Sub TEST6()
i = 1
'「For Each」でループする
For Each a In Worksheets
If a.Name <> "Sheet1" Then '集計するシートではない場合
i = i + 1 'カウントアップ'VLookup関数で検索する
Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.VLookup("B", a.Range("A2:B15"), 2, False)
End If
Next
End Sub
Sub TEST7()
With Worksheets("Sheet1")
'VLookup関数で別シートを参照
.Cells(2, "A") = "=VLOOKUP(""B"",Sheet2!A2:B15,2,FALSE)"
.Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
End With
End Sub
検索値の「"B"」は「"」で囲って、「""B""」とするところがポイントです。
今回は値がほしいので最後に値に変換しています。
別シートを用意しておきます。
別シートを用意
では、VBAコードを実行してみます。
別シートを参照して検索
「VLookup関数」で「別シート」を参照して検索できました。
セルに数式を入力してコピーして使う
「埋め込み数式」を使う場合は、数式をコピーして使うと簡単です。
数式をコピーして使う
この数式をコピーして使います。
シート番号を使って別シートを参照する
「シート番号」を使って別シートを参照してみます。
「数式埋め込み」だとシート番号ではなく「シート名」が欲しいので「.Name」を使います。
「シート番号」から「シート名」するイメージ
「シート番号」を使って別シートを参照するVBAコードは、こんな感じになります。
「シート番号」を使って別シートを参照
Sub TEST8()
With Worksheets("Sheet1")
a = Worksheets(2).Name 'シート名を取得'VLookup関数で別シートを参照
.Cells(2, "A") = "=VLOOKUP(""B""," & a & "!A2:B15,2,FALSE)"
.Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
End With
End Sub
では、VBAコードを実行してみます。
「シート番号」を使って別シートを参照できました。
ちょっとややこしいですけど、「数式埋め込み」でも「シート番号」を使って別シートを参照できます。
全シートをループして参照する
「数式埋め込み」を使って、全シートをループして参照してみます。
先ほど使った「シート番号」を変数にしてループします。
全シートをループして参照
Sub TEST9()
With Worksheets("Sheet1")
'ループする
For i = 2 To 5
a = Worksheets(i).Name 'シート名を取得'VLookup関数で別シートを参照
.Cells(i, "A") = "=VLOOKUP(""B""," & a & "!A2:B15,2,FALSE)"
Next
.Range("A2:A5").Value = .Range("A2:A5").Value '値に変換
End With
End Sub
集計用のSheet1を用意します。
参照したい別ブックを用意しておきます。
「Sheet2～Sheet5」を用意しました。
では、VBAコードを実行してみます。
「VLookup関数」で全シートをループして参照できました。
「シートの数」を取得してループ
全てのシートをループする場合は、「シートの数」を取得してループします。
先ほどと同じですね。
シートの数は、「Worksheets.Count」を使いまs。
Sub TEST10()
With Worksheets("Sheet1")
'「最終シート」までループ
For i = 2 To Worksheets.Count
a = Worksheets(i).Name 'シート名を取得'VLookup関数で別シートを参照
.Cells(i, "A") = "=VLOOKUP(""B""," & a & "!A2:B15,2,FALSE)"
Next
.Range("A2:A5").Value = .Range("A2:A5").Value '値に変換
End With
End Sub
では、VBAコードを実行してみます。
「シートの数」を取得してすべてのシートをループできました。
「For Each」を使ってループ
もちろん、「For Each」を使ってループすることもできます。
集計用の「Sheet1」は飛ばしてループします。
Sub TEST11()
With Worksheets("Sheet1")
i = 1
'「For Each」でループする
For Each a In Worksheets
If a.Name <> "Sheet1" Then
i = i + 1 'カウントアップ'VLookup関数で別シートを参照
.Cells(i, "A") = "=VLOOKUP(""B""," & a.Name & "!A2:B15,2,FALSE)"
End If
Next
.Range("A2:A5").Value = .Range("A2:A5").Value '値に変換
End With
End Sub
