Excel VBAでBファイルのデータをXLOOKUPしAファイルに値を転記する方法

Excelで、AファイルにBファイルのデータをXLOOKUPで取得して値として保存したい場合、VBAを活用することで自動化できます。特に、Bファイルがシステムから抽出され、一時的に表示されるだけで保存されない場合でも対応可能です。

1. 基本方針

VBAでの手順は以下の通りです。

• Aファイルの最終行と最右列の空白セルを取得
• BファイルのA列を検索範囲、C列を戻り範囲としてXLOOKUPを実行
• 見つからなかった場合は0を入力
• 取得した結果は値としてAファイルに転記

2. VBAコード例

以下のコードはXLOOKUPの代わりにApplication.WorksheetFunction.XLookupを使用しています。

Sub XLookupFromB()    Dim wsA As Worksheet    Dim wsB As Worksheet    Dim lastRowA As Long    Dim lastColA As Long    Dim rngA As Range    Dim i As Long    Dim lookupVal As Variant    Dim result As Variant    Set wsA = ThisWorkbook.Sheets(1) 'Aファイルの対象シート    Set wsB = Workbooks("Bファイル名.xlsx").Sheets(1) 'Bファイルのシート    'Aファイルの最終行    lastRowA = wsA.Cells(wsA.Rows.Count, 1).End(xlUp).Row    'Aファイルの最右列の空白セル    lastColA = wsA.Cells(3, wsA.Columns.Count).End(xlToLeft).Column + 1    For i = 3 To lastRowA        lookupVal = wsA.Cells(i, 1).Value        On Error Resume Next        result = Application.WorksheetFunction.XLookup(lookupVal, wsB.Range("A:A"), wsB.Range("C:C"), 0)        On Error GoTo 0        wsA.Cells(i, lastColA).Value = result    Next iEnd Sub

3. 注意点

• Bファイルが開かれていない場合は、VBAで開く必要があります
• Application.WorksheetFunction.XLookupは見つからない場合エラーになるため、On Errorで0を代入しています
• 値として転記することでXLOOKUPの関数結果ではなく静的な値として保存されます

4. まとめ

Bファイルが保存されていない一時的な表示でも、VBAでXLOOKUPを用いればAファイルに必要なデータを転記可能です。最終行や最右列の取得もVBAで自動化できるため、効率的に処理できます。