VBA：XLOOKUPにてFor構文でループ処理する方法

前提・実現したいこと

エクセル・VBAの超初心者です。
VBAにXLOOKUP関数を使用して、別ブックのシートへデータを参照したいと思っています。

＜元データ＞データ名：「原料情報データA」　シート名：「原材料データ」
＜転記先＞データ名：「ThisWorkbook」にて記載　シート名：「配合規格書フォーマット」

元データについて、D列が検索No.で、スピル参照させたい文字・数値がE:AC列に入力されています。
転記先は、G列が検索対象No.で、スピル参照先をH:AF列に設定しています。

単行ではマクロが機能していますが、実際は250行ほどあるためFor構文でループ処理させたいのですが、うまく機能しません。

発生している問題・エラーメッセージ

実行時エラー 1004 アプリケーション定義またはオブジェクト定義のエラーです

該当のソースコード

エラーコードは以下の通りです。

Sub 配合規格書整備2()

Application.ScreenUpdating = False

Dim i

'「原料情報データA」を開く
Workbooks.Open ("D:\◎配合規格書反映フォーマット\新しいフォルダー\仮設定\原料情報データA.xlsx")

'For構文＆変数の定義「iは7～「配合規格書フォーマット」シートのG列最終入力セルまで」※繰り返し
For i = 7 To ThisWorkbook.Worksheets("配合規格書フォーマット").Range("G10000").End(xlUp).Row

'XLookup関数で、「原料情報データA」ブックの「原材料データ」シートをスピル参照する
ThisWorkbook.Worksheets("配合規格書フォーマット").Range("H & i:AF & i").Value = WorksheetFunction.XLookup(ThisWorkbook.Worksheets("配合規格書フォーマット").Range("G & i:G & i").Value, Workbooks("原料情報データA.xlsx").Worksheets("原材料データ").Range("D:D"), Workbooks("原料情報データA.xlsx").Worksheets("原材料データ").Range("E:AC"), "", 0, 1).Value

'「原料情報データA」を閉じる
Workbooks("原料情報データA.xlsx").Close

End Sub

試したこと

参考までに、機能している単体のXLOOKUP構文のコードは下記です。
ThisWorkbook.Worksheets("配合規格書フォーマット").Range("H7:AF7").Value = WorksheetFunction.XLookup(ThisWorkbook.Worksheets("配合規格書フォーマット").Range("G7").Value, Workbooks("原料情報データA.xlsx").Worksheets("原材料データ").Range("D:D"), Workbooks("原料情報データA.xlsx").Worksheets("原材料データ").Range("E:AC"), "", 0, 1).Value

補足情報（FW/ツールのバージョンなど）

独学でVBAの勉強を始めた超初心者です。解説動画やネットの解説を参考に構文を組んでみたのですが苦戦しており、ご教示時いただけますと幸いです。
どうぞよろしくお願いいたします。

行動規範の内容に同意します

回答1件

ベストアンサー

Rangeの指定の部分"H & i:AF & i" のあたりが原因だと思います。

VBA
1Sub 配合規格書整備2()
2    Application.ScreenUpdating = False
3    '「原料情報データA」を開く
4    Dim wb As Workbook
5    Set wb = Workbooks.Open("D:\◎配合規格書反映フォーマット\新しいフォルダー\仮設定\原料情報データA.xlsx")
6    
7    Dim moto As Worksheet, saki As Worksheet
8    Set moto = wb.Worksheets("原材料データ")
9    Set saki = ThisWorkbook.Worksheets("配合規格書フォーマット")
10    
11    'For構文＆変数の定義「iは7～「配合規格書フォーマット」シートのG列最終入力セルまで」※繰り返し
12    Dim i
13    For i = 7 To saki.Range("G" & saki.Rows.Count).End(xlUp).Row
14        'XLookup関数で、「原料情報データA」ブックの「原材料データ」シートをスピル参照する
15        saki.Range("H" & i & ":AF" & i).Value = WorksheetFunction.XLookup(saki.Range("G" & i).Value, moto.Range("D:D"), moto.Range("E:AC"), "", 0, 1).Value
16    Next
17    
18    '「原料情報データA」を閉じる
19    wb.Close False
20    
21    Application.ScreenUpdating = True
22End Sub