VBAでFormula関数を使ってVlookup関数とMatch関数の組合せを入力させる方法

VBA初心者であるのでどなたかお助けいただけますと幸いです。
Sheet2のセルに、Sheet3に同じくVBAで用意するピボットをデータテーブルとしたVlookup関数をFormula関数で入力したのですが、下記で処理するとどうしてもすべて0(エラー)となってしまいます。
列番号をMatch関数で参照するところでうまくいっていないことはわかるのですが、どのように対応すればいいか、どなたかお知恵いただけますでしょうか・・・

For i = 1 To X

K = Worksheets(1).Range("A1") + i
　　　　'Match関数において、検索値を変数にする
　　　　（Sheet(1)で設定している列番号からループで右に処理していく)

Set d = Worksheets(2).Cells(1, K)　
　　　　'Match関数において、Sheet(2)の1行目・変数K列を検索値とする
Set pd = Worksheets(3).Range("2:2")
　　　　'Match関数において、Sheet(3)の2行目にあるピボットテーブルの行ラベルを検索範囲とする

Set arr = Worksheets("ピボット").Range("A4").CurrentRegion.Offset(1, 0)

On Error Resume Next

retsu = WorksheetFunction.Match(d, pd, 0)
　　　　'Vlookup関数の列番号をMatch関数で可変させる

Worksheets(2).Select

If retsu <> "" Then Range(4, K).Formula = "=IFERROR(Vlookup(C2, " & arr.Address(External:=True) & ", Match(d, pd, 0) , False),0)"
If retsu = "" Then Cells(4, K).Formula = "0"

Next i


どなたかお助けいただけますでしょうか・・・・
よろしくお願いします。

No.2 ベストアンサー 回答者： siffon9 回答日時： 2018/07/17 09:42

エラーが出ない様に書き直してみました。

各シートのデータの配置等が不明、変数の型が不明ですので推測しました。
諸々修正すべき箇所があるかもしれません。


Dim X as long
Dim i As Long
Dim k As Long
Dim d As String
Dim pd As Range
Dim arr As Range
Dim retsu As Long

X = 100 'この値は適当にいれました

For i = 1 To X
　　k = Worksheets(1).Range("A1").Value + i
　　d = Worksheets(2).Cells(1, k).Value
　　Set pd = Worksheets(3).Range("2:2")
　　Set arr = Worksheets("ピボット").Range("A4").CurrentRegion.Offset(1, 0)

　　retsu = 0
　　On Error Resume Next
　　retsu = WorksheetFunction.Match(d, pd, 0)
　　On Error GoTo 0

　　Worksheets(2).Activate

　　If retsu > 0 Then
　　　　Worksheets(2).Cells(4, k).Formula = "=IFERROR(VLookUp(C2, " & arr.Address(External:=True) & "," & retsu & ", False), 0)"
　　ElseIf retsu = 0 Then
　　　　Worksheets(2).Cells(4, k).Formula = "0"
　　End If
Next i

この回答へのお礼

siffon9様
つたない説明を読み取って丁寧にご回答頂きありがとうございます…ご教示いただいた内容で修正したところ期待していた動きをしてくれました！！本当にありがとうございます。記載下さったErrorの回避方法含めきちんと勉強していきたいと思います。

ありがとうございました！

お礼日時：2018/07/17 23:29

No.1 回答者： runix2007 回答日時： 2018/07/17 00:12

そうだね