Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Microsoft 365)
XLOOKUPについて
投稿日時: 22/07/16 18:29:16
投稿者: taklions

同じエクセルブックの発送先集計シートにあるデータ(G,H,I列)をF列を参照してラベル明細シートにXLOOKUPを使って書き込みたいのですが、result7 = WorksheetFunction.XLookup(Cells(m, 1), Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6)), Worksheets("発送先集計").Range(Cells(5, 7), Cells(EndLine1, 9)))
がエラーになってしまいます。 どこがまちがっているのでしょうか?
 
Dim EndLine1 As Long 'Sheet(発送先集計)の列Fの最終行番号を変数に
Dim Endline2 As Long 'Sheet(ラベル明細)の列Aの最終行番号を変数に
Dim m As Long
Dim result7 As Variant 'XLookupの戻り値
 
EndLine1 = Worksheets("発送先集計").Range("F1048576").End(xlUp).Row
 
ThisWorkbook.Sheets("ラベル明細").Activate
     
Endline2 = ActiveSheet.Range("A1048576").End(xlUp).Row
 
For m = 2 To Endline2
           
result7 = WorksheetFunction.XLookup(Cells(m, 1), Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6)), Worksheets("発送先集計").Range(Cells(5, 7), Cells(EndLine1, 9)))
 
If Not IsError(result7) Then
                Range(Cells(m, 13), Cells(m, 15)) = result7
            End If
Next

回答
投稿日時: 22/07/16 18:33:59
投稿者: WinArrow
投稿者のウェブサイトに移動

普通にセルに数式を入力してみて、
違いを確認してみましたか?

投稿日時: 22/07/16 18:47:21
投稿者: taklions

WinArrow さんの引用:
普通にセルに数式を入力してみて、
違いを確認してみましたか?

 
以下はマクロの記録で これよりVBAを作成しようとしたのですが、わかりませんでした
 
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-12],発送先集計!R5C6:R37C6,発送先集計!R5C7:R37C8)"
    Range("M2:N2").Select
    Selection.AutoFill Destination:=Range("M2:N34"), Type:=xlFillDefault
    Range("M2:N34").Select

回答
投稿日時: 22/07/16 22:09:18
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

result7 = WorksheetFunction.XLookup(Cells(m, 1), Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6)), Worksheets("発送先集計").Range(Cells(5, 7), Cells(EndLine1, 9)))


引用:

マクロの記録
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-12],発送先集計!R5C6:R37C6,発送先集計!R5C7:R37C8)"

引数を比較してみましょう
 
前者を「A」、後者を「B」とします。
第1引数は、同じとします。
 
「A」の第2引数、第3引数のセルの指定が、間違っています。
第2引数
Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6))
の中の「Cells(5,6)」「Cells(EndLine1, 6))」、が問題個所です。
Rangeはシートで修飾しているので、問題あないが、Cellsは、シートで修飾されていないので、
ActiveSheetとなります。
 
この部分を別にして、Range変数に入れましょう。

Dim 範囲1 As Range,範囲2 As Range
  With Worksheets("発送先集計")
      Set 範囲1 = .Range(.Cells(5, 6), .Cells(EndLine1, 6))
      set 範囲2 = .Range(.Cells(5, 7), .Cells(EndLine1, 9))
  End With
※このコードは、ループの外で1回だけ実行すればよいでしょう。
元のコードの修正
result7 = WorksheetFunction.XLookup(Cells(m, 1), 範囲1, 範囲2)
 
※こちらは、Exel2019の環境で「XLookup」は使えないため、テストはしていません。
※第3引数のセル範囲が、コードとマクロ記録と違っています。確認してください。
※蛇足ですが、Cellsで記述の場合、「列」を数字で記述より、英字の方が分かりやすいと思いますが、
 そこは、趣味の世界なので・・・・

投稿日時: 22/07/16 22:45:56
投稿者: taklions

WinArrow さんの引用:
引用:

result7 = WorksheetFunction.XLookup(Cells(m, 1), Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6)), Worksheets("発送先集計").Range(Cells(5, 7), Cells(EndLine1, 9)))


引用:

マクロの記録
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-12],発送先集計!R5C6:R37C6,発送先集計!R5C7:R37C8)"

引数を比較してみましょう
 
前者を「A」、後者を「B」とします。
第1引数は、同じとします。
 
「A」の第2引数、第3引数のセルの指定が、間違っています。
第2引数
Worksheets("発送先集計").Range(Cells(5, 6), _ Cells(EndLine1, 6))
の中の「Cells(5,6)」「Cells(EndLine1, 6))」、が問題個所です。
Rangeはシートで修飾しているので、問題あないが、Cellsは、シートで修飾されていないので、
ActiveSheetとなります。
 
この部分を別にして、Range変数に入れましょう。

Dim 範囲1 As Range,範囲2 As Range
  With Worksheets("発送先集計")
      Set 範囲1 = .Range(.Cells(5, 6), .Cells(EndLine1, 6))
      set 範囲2 = .Range(.Cells(5, 7), .Cells(EndLine1, 9))
  End With
※このコードは、ループの外で1回だけ実行すればよいでしょう。
元のコードの修正
result7 = WorksheetFunction.XLookup(Cells(m, 1), 範囲1, 範囲2)
 
※こちらは、Exel2019の環境で「XLookup」は使えないため、テストはしていません。
※第3引数のセル範囲が、コードとマクロ記録と違っています。確認してください。
※蛇足ですが、Cellsで記述の場合、「列」を数字で記述より、英字の方が分かりやすいと思いますが、
 そこは、趣味の世界なので・・・・

 
WinArrow 様
ご説明いただいたようにCellsをシートで修飾しましたところ、解決しました。
ご丁寧な説明ありがとうございました。