VBAでvlook up関数を使いたい。

初心者です。コードの書き方が分からず困っています。教えてもらえますか？

(1)"リスト"に下記例の通り、２列にデータが並んでいる。
　A列2行目には品番、B列2行目には部品と記載されている。

（例）
品番　　　　　記載内容
11111-1111　　部品
11111-1112　　部品
11111-1113　　部品


(2)別シート"終了品"のD列の3行目～は、品番が"リスト"とは
異なる順序で並んでいる（もしくは存在しない）。また同一品番が複数存在するこ
ともある。
この品番のZ列に部品とセットしたい。データ数は日ごとに変動するが多くて200
行。



D列・・・・・・・・・・・・Z列


品番
11111-1113　　 　部品
11111-1112　　 　部品
44444-1112　　 　空欄にしたい





見様見真似で作成してみましたが、うまくいきません。。





Sub　部品記載（）


Dim r As Range
With Worksheets("終了品")
.Columns(2).ClearContents
Set r = Range("D200").End(xlUp).Offset(0, 23)
.Range("D3", r) = "=IF(ISERROR(VLOOKUP(A2,リスト!$A$2:$B$4,2,FALSE))" &
_
",""""," & "VLOOKUP(A2,リスト!$A$2:$B
$4,2,FALSE))"
End With

End　Sub

よろしくお願い致します。

質問者からの補足コメント

• 皆さま、音信不通で申し訳ありません。
  １つずつ確認し勉強します。
  はじめての投稿で記入する欄が間違っていたら申し訳ありません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/09/02 15:40

• ぶうじーサマ
  VLOOKUP関数は作成できましたが、
  そのシートには別のマクロが組んでありまして既存の値を削除する。となっています。
  そのボタンを押してしまうとセルに入れてた関数がきえてしまうのでVBA内にVLOOKUP関数を入れたかったのです。
  
  くんこばサマ
  WorksheetFunctionオブジェクト
  これです！
  ありがとうございます‼︎
  
  Qchan 1962サマ
  わかりやすいように説明してくださってありがとうございます。
  久々にマクロを作成しようと思ったら英語に見えて何がなんだかわかりません。ただただ未熟者です。
  添付いただいたURLを確認致します。
  今思うと、関数の消えない削除の仕方を質問するべきだったカモしれません。
  親切丁寧にありがとうございます。
  
  文字数オーバーになってしまうので、次の補足に続きます。

    補足日時：2019/09/02 19:30

• Wind Fallerサマ
  記述までご記載いただきありがとうございます。
  返信するにも期間があるのか、最初の投稿に今でも返信ができるのかがわかっておらず申し訳ありません…調べてみます。
  「回答する」をクリックしたら自分の質問には回答できないようでして…
  
  皆さま本当にありがとうございます。
  返信しない という失礼な態度をしてしまい大変申し訳ございませんでした。
  
  補足ありというところでぶうじーサマ宛？に記入しました。
  お一人ずつ返信したいのですが申し訳ありません。
  ぶうじーサマ以外の方にも補足ありと記載があれば記入できるのですが、他の方には補足ありが表示されておらず。(見落としかもしれません(>_<))
  本当に申し訳ありません。

    補足日時：2019/09/02 19:32

No.1 回答者： ぶうじー 回答日時： 2019/08/30 17:03

=IF(ISERROR(VLOOKUP(A2,リスト!$A$2:$B$4,2FALSE)),"",VLOOKUP(A2,リスト!$A$2:$B$4,2FALSE))

ではないでしょうか。最初のVLOOKUPでエラーならばスペースを、エラーでなければ後のVLOOKUPで検索値を表示する。

No.2 回答者： くんこば 回答日時： 2019/08/30 17:34

WorksheetFunctionオブジェクト

これ、使わないの？

No.3 回答者： Qchan1962 回答日時： 2019/08/30 18:12

表題に反する回答で、申し訳ありません。

これは、セルに数式を入力したいのだと思いますが
VBAで解を入力するのではだめでしょうか？
せっかくVBAを使用していますし、数式だと重くなる？まあ、大したことではありませんが。

数式を入力する場合　”＝1+1”　で入力できます。
従って、セルで実際に入力して検証できます。エラーのない数式を　”で囲み文字列にしてVBAで入力します。
ご掲示の”＝・・・”は、エラーがあるようですので確認されてみてください。

正確に数式を入力したのちに行などの変更は、やり方がいくつか考えられます。
Forなどのカウント変数で　”＝D"　& カウント変数 "+5"
Sub test()
Dim i
For i = 1 To 5
Range("c" & i) = "＝D" & i & "+5"
Next
End Sub

これを実行すると　D1には=D1+5が入力されます。

また、絶対参照などを確認しVBAで入力した数式をVBAを使いオートフィルを行うことも出来ます。
https://www.moug.net/tech/exvba/0050146.html

直接VBAで解を出す方法
作成者の好みや仕様、汎用性で変わりますが、やり方は、多くあります。

私は、Match派？なので、、下記にて同様の動作をするであろうコードを示します。
VLOOKUPは、他の方が回答すると思いますので、あえて。。

また、参考として、ワークシート関数をVBAで使用する場合は、WorksheetFunction.を入れます。
参考：https://www.atmarkit.co.jp/ait/articles/1506/19/ …

Sub Sample()
Dim r As Range
Dim cnt As Long, MaxRow As Long
Dim i As Long, j As Long
MaxRow = Worksheets("終了品").Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For Each r In Range("A1:A" & Worksheets("リスト").Cells(Rows.Count, 1).End(xlUp).Row)
i = 1
With Worksheets("終了品")
cnt = WorksheetFunction.CountIf(.Range("D:D"), r)
For j = 0 To cnt
i = WorksheetFunction.Match(r, .Range("D" & i & ":D" & MaxRow), 0) + i
.Range("Z" & i - 1) = r.Offset(0, 1)
Next
End With
Next
End Sub

細部は気にしてませんが、表組の解釈が違っていたら、すみません。

No.4 回答者： Qchan1962 回答日時： 2019/08/30 18:25

誤字、不具合訂正　すみません。

誤：D1には=D1+5が入力されます。
正：C1には=D1+5が入力されます。

誤：MaxRow = Worksheets("終了品").Cells(Rows.Count, 1).End(xlUp).Row　
矛盾する可能性あり

正：MaxRow = Worksheets("終了品").Cells(Rows.Count, 4).End(xlUp).Row　
品番記載列＝D列（カラム4）

この回答へのお礼

親切にありがとうございますm(__)m
大変助かりました。

お礼日時：2019/09/02 22:31

No.5 ベストアンサー 回答者： Qchan1962 回答日時： 2019/08/30 19:49

シーンなので、

示されている数式を理解していませんが、、
Z3以降にD列該当行でVlookUp数式を入力するVBA
参考に、、、どうぞ

Sub 部品記載()
Dim i As Long, MaxRow As Long
MaxRow = Worksheets("終了品").Cells(Rows.Count, 4).End(xlUp).Row
With Worksheets("終了品")
.Columns(2).ClearContents
For i = 3 To 200
.Range("Z" & i).Value = "=IFERROR(VLOOKUP(D" & i & ",リスト!A2:B100,2,FALSE),"""")"
Next
End With
End Sub

おそらく、空白””の部分などが問題なのではないでしょうか
＆の使い方も検証されては、と

No.6 回答者： Qchan1962 回答日時： 2019/08/30 23:17

VBAでVLOOKUP数式を入力

For i = 3 To MaxRow
.Range("Z" & i).Value = "=IFERROR(VLOOKUP(D" & i & ",リスト!A2:B10,2,FALSE),"""")"
Next

VBAでVLOOKUPを使って解を入力

With Worksheets("終了品")
On Error Resume Next　’IFERRORに該当する部分
.Range("Z3:Z" & MaxRow).ClearContents　’　””””に該当する部分
For i = 3 To MaxRow
.Range("Z" & i).Value = WorksheetFunction.VLookup(Range("D" & i).Value2, Worksheets("リスト").Range("A2:B10"), 2, False)
Next

＃５のコードの該当する箇所を入れ替え変更すると使用できます。

On Error Resume Nextは、IFERRORに該当しますが
厳密には、違います。
同じ、VLOOKUP関数ですが書き方が大分違いますね。

No.7 回答者： WindFaller 回答日時： 2019/09/01 12:48

少し割り込みさせていただきますが、質問者さんは、この回答に関して、一言も答えていないのですね。

'//標準モジュール
Sub 部品記載r()
　Dim Rng As Range
　With Worksheets("終了品")
　　.Columns(26).ClearContents　'Z列をクリアする
　　On Error Resume Next　'SpecialCells のエラーに対するもの
　　With .Range("D2", .Cells(Rows.Count, "D").End(xlUp)).Offset(, 22)　　'Z列
　　　.FormulaLocal = "=VLOOKUP(D2,リスト!$A$2:$B$4,2,FALSE)"
　　　.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
　　　.Value = .Value　'数式を値に変更する
　　End With
　　On Error GoTo 0
　End With
End Sub

*R1C1方式で数式を書くと不評が多いようなので、A1方式で書きます。

No.8 回答者： Qchan1962 回答日時： 2019/09/01 18:03

WindFallerさん　そうなんですか、

おかしな質問（コピペ、削除の空白）で、ノンリアクションなんで、変だと思いました。

今回もきっと無いですかね。