Excel VBAでのVLOOKUP 別ブックに検索範囲を設定したいです。

Question

Excel VBAで、VLOOKUP関数を最終行まで処理させようとしています。
しかし、検索範囲を別ブックにしたとたん動きません。エラーにもなりません。
以下のソースで、変数SearchRange　の書き方は間違っているでしょうか。

該当のソースコード

Dim SearchWord As String
Dim SearchRange As Range
Dim WordMaxRow As Long
Dim i As Long

WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row
Set SearchRange = Workbooks("練習２マスタ.xlsx").Worksheets(1).Range(Cells(1, 1), Cells(99, 2))

Comments

[yureighost]

処理の全ソースが提示されていないようで、
こちらで検証できないため推測で書きますが多分Range内のCellsの記述がまずいです。

特に指定なしでCellsで取得するとActiveSheetのセルを取得します。
ActiveSheetシートの切り替えを別ブックにしていないなら
マクロを動作させた時のシートがActiveSheetシートになっているため、
正しく別ブックのシートが選択できていません。

Dim ws As WorkSheet
Set ws = Workbooks("練習２マスタ.xlsx").Worksheets(1)
Set SearchRange = ws.Range(ws.Cells(1, 1), ws.Cells(99, 2))

多分こんな感じで書けばいけるんじゃないでしょうか。

[amatida]

yurei様　返信ありがとうございます！

ご指摘を踏まえて、workbook、Sheetにも変数を設定してみました。

が、やはりSerachRnageの設定のところでエラーがおきてしまいます。

ここです⇒ Set SearchRange = wb.ws.Range(Cells(1, 1), Cells(99, 2))

Rengeの書き方が間違っているのでしょうか

以下作ったソースの全容です。

Sub 参照〇付与()

Dim SearchWord As String
Dim wb As Workbook
Dim ws As Worksheet
Dim SearchRange As Range
Dim WordMaxRow As Long
Dim i As Long

Set wb = Workbooks("練習２マスタ.xlsx")
Set ws = wb.Worksheets(1)
Set SearchRange = wb.ws.Range(Cells(1, 1), Cells(99, 2))

WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next

For i = 2 To WordMaxRow

SearchWord = Cells(i, 2)

Cells(i, 1) = Application.WorksheetFunction.VLookup(SearchWord, SearchRange, 2, False)

Next i

On Error GoTo 0

End Sub

[yureighost]

また動作確認はしてませんが、恐らく
wb.ws.Range(Cells(1, 1), Cells(99, 2))
の記述がまずいかと。
wsにwbの指定は既に含まれているのでwbは必要ありません。
またCellsの方にwsを指定してやる必要があります。

ws.Range(ws.Cells(1, 1), ws.Cells(99, 2))
これで行けるのではないかと。

[amatida]

ありがとうございます！エラーなく動きました！助かりました！

Answer 1

処理内容が不明な為、取り合えずエラーと不具合部分等々を修正してみました。

Sub 参照〇付与()
    Dim SearchWord As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SearchRange As Range
    Dim WordMaxRow As Long
    Dim i As Long

    Set wb = Workbooks("練習２マスタ.xlsx")
    Set ws = wb.Worksheets(1)
    Set SearchRange = ws.Range(ws.Cells(1, 1), ws.Cells(99, 2))
    WordMaxRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
    On Error Resume Next
    For i = 2 To WordMaxRow
        SearchWord = ws.Cells(i, 1)
        ws.Cells(i, 1) = Application.WorksheetFunction.VLookup(SearchWord, SearchRange, 2, False)
    Next i
    On Error GoTo 0
End Sub

Answer 2

Set SearchRange = Workbooks("練習２マスタ.xlsx").Worksheets(1).Range(Cells(1, 1), Cells(99, 2))

ただ単にCellsと書くと、アクティブシートを指します。
練習２マスタ.xlsxの方は指していないのでエラーになります。
以下のようにきちんとどこのシートなのかを明示してあげましょう。

べたに書く場合

Set SearchRange = Workbooks("練習２マスタ.xlsx").Worksheets(1).Range(Workbooks("練習２マスタ.xlsx").Worksheets(1).Cells(1, 1), Workbooks("練習２マスタ.xlsx").Worksheets(1).Cells(99, 2))

Withを使う場合

With Workbooks("練習２マスタ.xlsx").Worksheets(1)
    Set SearchRange = .Range(.Cells(1, 1), .Cells(99, 2))
End With

シートをオブジェクト変数に格納しておく場合

Dim sh As Worksheet
Set sh = Workbooks("練習２マスタ.xlsx").Worksheets(1)
Set SearchRange = sh.Range(sh.Cells(1, 1), sh.Cells(99, 2))

ていうか範囲が固定であるならこれでいいような。

Set SearchRange = Workbooks("練習２マスタ.xlsx").Worksheets(1).Range("A1:B99")