[[20221019091916]]　『WorksheetFunctionで VLookupできない』（花）　ページの最後に飛ぶ

『WorksheetFunctionで VLookupできない』（花）

Sheet1のA列にコードB列にメアド、Sheet２のB列にコード一覧とD列にメアド一覧があり、Sheet1のB2セルに=VLOOKUP(A2,Sheet2!B:D,3,FALSE)、B3セルに=VLOOKUP(A3,Sheet2!B:D,3,FALSE)と下にVLOOKUP関数が並び、Sheet2のメアド一覧を参照しています。VBA化で下記コードでエラー「WorksheetFunctionクラスのVlookupプロパティを取得できません。」となり、デバック
「 Sht1.Cells(i, 2) = Application.WorksheetFunction. _

                VLookup(Target, MyArea, 3, False)」で止まっています。初心者のため理解できないので解決策を教えていただけませんでしょうか。お願いします。
Sub Vlookup関数で値取得()
Dim i As Long
Dim Target As String
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim MyArea As Range
    'シートを変数格納
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    '列を参照範囲に格納
    Set MyArea = Sht2.Range("B:D")
    '2～600行目をループ
    For i = 2 To 600
        '「名前」を取得
        Target = Sht2.Cells(i, 2).Value
        With ActiveSheet
            Sht1.Cells(i, 2) = Application.WorksheetFunction. _
                VLookup(Target, MyArea, 3, False)
        End With
    Next i
End Sub

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 >Target = Sht2.Cells(i, 2).Value
 これだとSheet1のA列の値ではなくSheet2のB列の値を検索値にしようとしているが。
 Target = Sht1.Cells(i, 1).Value
 では？

 あと
 >With ActiveSheet
 >End With
 は不要では？
（ねむねむ） 2022/10/19(水) 09:36:27

ご指摘ありがとうございます。下記へ修正しましたが同じ個所で同じエラーとなります。
Sub Vlookup関数で値取得()
Dim i As Long
Dim Target As String
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim MyArea As Range

    'シートを変数格納
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    '列を参照範囲に格納
    Set MyArea = Sht2.Range("B:D")
    '2～600行目をループ
    For i = 2 To 600
        '「名前」を取得
        Target = Sht1.Cells(i, 1).Value
                 Sht1.Cells(i, 2) = Application.WorksheetFunction. _
                 VLookup(Target, MyArea, 3, False)
        Next i
End Sub
（花） 2022/10/19(水) 10:31:30

 Sheet1のコードは確かにSheet2に存在するのだろうか？
 存在しないコードがあってエラーになっているのではないだろうか？
（ねむねむ） 2022/10/19(水) 10:57:35

 もしコードが数値の場合、
 >Dim Target As String
 を
 Dim Target As Variant
 で試してみてくれ。

 これはVLOOKUP関数では数値と文字列の数字とは別とみなすため。
（ねむねむ） 2022/10/19(水) 11:08:02

あれ、書き込まれてない。

だから、Targeの値が無いんですよ。
WorksheetFunction
を消してください。
エラー値が書き込まれるはず。

（編集） 2022/10/19(水) 11:12:41

Dim Target As Variantでも同じ個所で同じエラーでした。何がいけないのでしょう。
Sheet1のセルB2に関数=XLOOKUP(A2,Sheet2!B:B,Sheet2!D:D)をセットすると表示されるのでコードは存在しているようです。

（花） 2022/10/19(水) 11:40:32

コードの具体例を挙げて下さい。
数字だけのものですか？数字以外の文字列もあるんですか？
一方が "0100"で、一方が 100　といったことは無いですか？

 　
（γ） 2022/10/19(水) 11:49:07

横からですが何点か。

■１
提示のコードを整理するとこうなりますよね。

    Sub Vlookup関数で値取得_整理()
        Dim i As Long

        For i = 2 To 600 Step 1
            Sheets("Sheet1").Cells(i, "B").Value = _
                Application.WorksheetFunction.VLookup( _
                    Sheets("Sheet1").Cells(i, "A").Value, _
                    Sheets("Sheet2").Range("B:D"), _
                    3, _
                    False)
        Next i
    End Sub

まずはステップ実行して、皆さんが言うように何行目の検索値でエラーになるのか調べて見てはどうでしょうか？

■２
考え方によりますが、単純に

 (1)数式を書き込む
 (2)値に変換する

というように考えれば↓のようにもできますよね。

    Sub 別案()
        With Sheets("Sheet1").Range("B2:B600")
            .Formula = "=VLOOKUP(A2,Sheet2!B:D,3,FALSE)"
            .Value = .Value
        End With
    End Sub

アレコレ悩むよりこちらの方がスマートだったりしませんか？

（もこな２） 2022/10/19(水) 14:13:11

 Sht1.Cells(i, 2) = Application.WorksheetFunction. _
                 VLookup(Target, MyArea, 3, False)
 Tagetやめて
 Sht1.Cells(i, 2) = Application.WorksheetFunction. _
                 VLookup(Sht2.Cells(i, 2), MyArea, 3, False)

 もしくは
 Dim Target As Variant で
 Target = Sht2.Cells(i, 2).Value2
（編集） 2022/10/19(水) 15:34:27

みなさんありがとうございます。初心者には、もこな２さんの別案が理解しやすいです。そこで追加の質問ですが、With Sheets("Sheet1").Range("B2:B600")のB600を変数にする方法は、下記のコードで
With Sheets("Sheet1").Range("B2:B600")をどのように変更すればよいか教えてください。
　Sub 別案()
　　Dim i As Long

    For i = 1 To Cells(Rows.Count,1 ).End(xlUp).Row

        With Sheets("Sheet1").Range("B2:B600")
            .Formula = "=VLOOKUP(A2,Sheet2!B:D,3,FALSE)"
            .Value = .Value
        End With
    End Sub

（花） 2022/10/19(水) 16:28:37

 皆さんあれこれ言っていますけど
 シート上ではエラーがあるとエラー名が表示されて教えてくれますよね。
 マクロから見れば「そんなの存在しない」ということでエラー表示が出るんですよ。
 エラーが出るような式はマクロに対してその対策をしておくことが重要です。

 対策として一例ですが
     Target = Sht2.Cells(i, 2).Value
     If Sht2.Cells(i, 2) = "" Then Exit For　←これ追加(途中に空白がるとそこで終わり。)
   With ActiveSheet
 として For から抜ける。
 試してみてください。

（?） 2022/10/19(水) 16:59:00

WorksheetFunction
を消してください。
エラー値が書き込まれるはず。

これに対して、あると申しておりますので。
あるのに見つからないと言う事で
多分日付。
ですから、検査値を変数に入れないで、直接セルを参照するか
.value2
でシリアル値で探すと提案したんですが。
質問者は回答をほとんど読んでない上に試してみようとか理解しようとかしてないみたいですが。
（編集） 2022/10/19(水) 20:30:50

 （編集） 2022/10/19(水) 15:34:27
 での回答を試してみました。
 質問者が言っているエラーが表示されますよ。
「質問者は回答をほとんど読んでない上に試してみようとか理解しようとかしてないみたいですが。」
 というより前に実際に試行してみたんですか。
（?） 2022/10/19(水) 20:51:16

どこ見てんの？

（編集） 2022/10/19(水) 11:12:41

 （編集） 2022/10/19(水) 15:34:27
は、検査値があると言うので日付ではないか？との対応策。
こっちはあると言うが前提。
（編集） 2022/10/19(水) 21:08:34

どこ見てんの？じゃないでしょ。
ちゃんと試行したのか聞いてんだよ。
このあほ。
（?） 2022/10/19(水) 21:12:18

022/10/19(水) 15:34:27
は、あると言うのでエラーの事は考えてない。

エラー値が欲しかったら最初に

WorksheetFunction
を消してください。

とかいたがカス雄君。
荒れるのでここで退散カス相手にしてられんは。
（編集） 2022/10/19(水) 21:25:24

なんか揉めてますが・・・

Target = Sht2.Cells(i, 2).Value
の中身がどういうタイプの値なのかでエラーになるならないが変わるので、

MsgBox TypeName(Target)
とTarget入力の次に入れておいて出た結果が"Date"なら編集さんがいう日付に当たり、
.Valueの部分を.Value2に変えればVlookUp関数のエラーは出ません。

もし他のタイプならそれを書いてもらえれば、解決へ近づくかと思います。
（補足） 2022/10/20(木) 11:58:00

 | コードの具体例を挙げて下さい。
 | 数字だけのものですか？数字以外の文字列もあるんですか？
 という質問を無視する理由を教えてください。原因を知りたいのではなかったのですか。
 コードを検索値とした照合の不一致の話なんでしょう？
 日付とかどうしてそういう方向に行くのかも理解できなかったですね。
 　
（γ） 2022/10/20(木) 12:59:31

https://daitaideit.com/vba-vlookup-avoid-error/#:~:text=Excel%20VBA%E3%81%A7%E3%80%81VLookup%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%BF%E3%81%86%E9%9A%9B%E3%81%AE%E3%80%8C%E3%82%A8%E3%83%A9%E3%83%BC%E3%82%92%E5%9B%9E%E9%81%BF%E3%80%8D%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E3%81%94%E7%B4%B9%E4%BB%8B%E3%81%97%E3%81%BE%E3%81%99%E3%80%82%20%E3%80%8CWorksheetFunction%E3%80%8D%E3%82%92%E4%BD%BF%E3%81%86%E5%A0%B4%E5%90%88%E3%81%AF%E3%80%8COn,Error%20Resume%20Next%E3%80%8D%E3%82%92%E4%BD%BF%E3%81%84%E3%81%BE%E3%81%99%E3%80%82
（名無し） 2022/10/20(木) 17:42:48

もうトピ主が現れることはなさそうですが、私も呟いておきます。

■１
「2022/10/19(水) 10:31:30」に提示されたコードのインデントだけ修正するとこうなります。

    Sub Vlookup関数で値取得()
        Dim i As Long
        Dim Target As String
        Dim Sht1 As Worksheet
        Dim Sht2 As Worksheet
        Dim MyArea As Range

        Set Sht1 = Sheets("Sheet1")
        Set Sht2 = Sheets("Sheet2")
        Set MyArea = Sht2.Range("B:D")

        For i = 2 To 600
            '「名前」を取得
            Target = Sht1.Cells(i, 1).Value

            Sht1.Cells(i, 2) = Application.WorksheetFunction.VLookup(Target, MyArea, 3, False)
        Next i
    End Sub

そして、「Sht1」「Sht1」「MyArea」も邪魔くさいので展開するとこうなります。

    Sub Vlookup関数で値取得()
        Dim i As Long
        Dim Target As String

        For i = 2 To 600
            '「名前」を取得
            Target = Sheets("Sheet1").Cells(i, 1).Value

            Sheets("Sheet1").Cells(i, 2) = Application.WorksheetFunction.VLookup(Target, Sheets("Sheet2").Range("B:D"), 3, False)
        Next i
    End Sub

で、今回の「WorksheetFunctionクラスのVlookupプロパティを取得できません。」というエラーは、皆さんが指摘されているようにVLookup関数の結果がエラーになっていると思いますので↓のように発言した次第です。
＞＞何行目の検索値でエラーになるのか調べて見てはどうでしょうか？

なお、エラーの原因は

 ＞Sheet1のA列にコードB列にメアド
 ＞'「名前」を取得
 ＞追加の質問ですが、With Sheets("Sheet1").Range("B2:B600")のB600を変数にする方法

などといってるところから推測すると、A列が日付型であるとか数値を文字列にしたことにより見つからなくなったというよりは、単純に「""」を検索値にしたのでVLookup関数がこけただけですね。たぶん。

なので、（?）さんがコメントされてるようにループを抜けるまではいかなくても、""ならばその行は処理しないとかすればよかったのだろうなとおもいます。

■２
＞With Sheets("Sheet1").Range("B2:B600")のB600を変数にする方法
落ち着いて考えればわかると思いますが。変化しうる部分は「B600」ではなく「600」の部分でしょう。
そして、最終行を調べる方法はご存じのようですから、固定部分と合体させてセル範囲を示す文字列を完成させるだけでよいだけでしょう。

    Sub 別案()
        Dim 最終行i As Long

        With Sheets("Sheet1")
            最終行 = .Cells(.Rows.Count, 1).End(xlUp).Row
            If 最終行 >= 2 Then
                .Range("B2:B" & 最終行).Formula = "=VLOOKUP(A2,Sheet2!B:D,3,FALSE)"
                .Range("B2:B" & 最終行).Value = .Range("B2:B" & 最終行).Value
            End If
        End With
    End Sub

（もこな２） 2022/10/20(木) 20:46:51

変数「最終行i」の「i」は必要ないんですよね。
（名無し） 2022/10/20(木) 21:08:48

＞変数「最終行i」の「i」は必要ないんですよね。
おっと　こりゃ失礼。
タイプ（書き換え）ミスしてますね。
ご指摘ありがとうございます。

（もこな２） 2022/10/20(木) 23:07:54

コメント返信：

[ 一覧(最新更新順) ]

[[20221019091916]] 『WorksheetFunctionで VLookupできない』（花） ページの最後に飛ぶ

[[20221019091916]]　『WorksheetFunctionで VLookupできない』（花）　ページの最後に飛ぶ