Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
WorksheetFunctionクラスの.VLookupプロパティは取得できない。
投稿日時: 18/09/14 01:11:41
投稿者: nktdai1755

vlookupのマクロの練習をしています。
デバッグを行うと、「WorksheetFunctionクラスの.VLookupプロパティは取得できない」とメッセージがでます。コードとexcel表になります。ご教授願えないでしょうか。
--------------------------------------
Sub sample()
Application.ScreenUpdating = False
Cells(2, 6) = Left(Cells(2, 1), 4)
Cells(2, 7) = WorksheetFunction.VLookup(Cells(2, 2), Sheets("sheet1").range("A:B"), 2, 0)※:ここ
If Cells(2, 5) >= 100 Then
   Cells(2, 8) = "A"
   Else
   Cells(2, 8) = "B"
End If
End Sub
------------------------------------------
A        B   C       D  E   F   G    H
販売年月    小売店    商品コード    商品    金額    年    支社    売上判定
20180901    愛知    1111       アサヒ    2000    2018        
20180902    愛知    2222       のどごし    200            
20180903    愛知    3333       サントリ    300            
20180904    愛知    4444       キリン    200            
20180905    愛知    5555      ウーロン茶    100            
20180906    愛知    6666      コーラ    110            
20180907    愛知    7777      コーヒ    120            
20180908    愛知    8888     ペプシ    130            
20180909    愛知    9999     オレンジ    140            
20180910    愛知    11110     ソーダ    150            
 

回答
投稿日時: 18/09/14 09:04:20
投稿者: WinArrow
投稿者のウェブサイトに移動

>WorksheetFunctionクラスの.VLookupプロパティは取得できない
 
このえらーは、検索キーと参照範囲のキーが一致しない
Cells(2, 2)の値が
Sheets("sheet1").range("A:B")のA列の中に存在しない
ということです。
 
考えられるのは、
Cells(2, 2)のシートを指定してないので、アクティブシートになっています。
キチンとシートで修飾しましょう。
 
 

投稿日時: 18/09/19 12:43:10
投稿者: nktdai1755

vlookupマクロがうまくいきません。
Private Sub sample()
 Application.ScreenUpdating = False
 Dim i
 With Sheets("データ追加")
 For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
 .Cells(i, 2)= WorksheetFunction.VLookup(.Cells(i, 2), Sheets("マスタ").Range("A:A"), 1,0)
 Next
 End With
End Sub
 
あたいが参照できてないのでしょうか。エラー箇所
 .Cells(i, 2)= WorksheetFunction.VLooとkup(.Cells(i, 2), Sheets("マスタ").Range("A:A"), 1,0)
 でエラーがでます。

回答
投稿日時: 18/09/19 13:03:05
投稿者: WinArrow
投稿者のウェブサイトに移動

>.Cells(i, 2)= WorksheetFunction.VLookup(.Cells(i, 2), Sheets("マスタ").Range("A:A"), 1,0)
-----(1)-----------------------------------(2)---------------------
 
(1)結果を受取るセル
(2)検索キーとなるセル
 
(1)と(2)が同じセルになっているけど、それでよいのかしら?

回答
投稿日時: 18/09/19 14:55:58
投稿者: Suzu

引用:
このえらーは、検索キーと参照範囲のキーが一致しない
Cells(2, 2)の値が
Sheets("sheet1").range("A:B")のA列の中に存在しない
ということです。

 
本当に検索値がない場合もあり得ますよね?
 
 
値の存在確認を行い、存在したら VLookUp を実行します。
 
If WorksheetFunction.CountIf(Worksheets("sheet1").Range("A:A"), .Cells(i, 2)) > 0 Then
 .Cells(i, 7) = WorksheetFunction.VLookup(.Cells(i, 2), Worksheets("sheet1").Range("A:B"), 2, 0)
End If

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

確認
 

引用:

    With Sheets("データ追加")
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(i, 2) = WorksheetFunction.VLookup(.Cells(i, 2), Sheets("マスタ").Range("A:A"), 1, 0)
        Next
    End With

↑掲示したコードにインデントをつけました。
コードを掲示する場合は、インデントを付けてくださいね・・・
 
シート名で修飾したのは、よしとして
最初の質問時のコートはだいぶ違いますね
VlookUpでエラーになったのは、
検索キーの値のデータ型と検索範囲のキーの部分のデータ型があっていないときにも発生します。
例えば
検索キーは、文字列
検索範囲の1列目は数値
という具合ですが、この場合目視で確認するだけではなく、
↓のような関数で確認したほうがよいです
=TYPE(A1)
セルA1が数値の場合は「1」、文字列の場合は「2」が返ります。
 
 

投稿日時: 18/09/28 03:06:16
投稿者: nktdai1755

 With Sheets("データ追加")
 For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
  Sheets("データ追加").Cells(i, 2) = WorksheetFunction.VLookup(Sheets("マスタ").Cells(i, 2), Sheets("データ追加").Range("A:A"), 1, 0)
  Next
End With
 
絶対パスで入力すると微妙に進みましたがシートが異なります。
データ追加シート:A2(検索値)
マスタシート:A2(検索範囲)
 
シートが異なりますが、大丈夫ですか。
WorksheetFunction.VLookup(Sheets("マスタ").Cells(i, 2), Sheets(!"データ追加").Range("A:A"), 1, 0)

回答
投稿日時: 18/09/28 08:36:44
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
絶対パスで入力すると微妙に進みましたがシートが異なります。
データ追加シート:A2(検索値)
マスタシート:A2(検索範囲)
  
シートが異なりますが、大丈夫ですか。
WorksheetFunction.VLookup(Sheets("マスタ").Cells(i, 2), Sheets(!"データ追加").Range("A:A"), 1, 0)

 
>シートが異なる
この部分は、意味不明
 
説明とコードが食い違います。
 
説明通りに記述すると
WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:A"), 1, 0)
 
となります。

回答
投稿日時: 18/09/28 08:44:21
投稿者: WinArrow
投稿者のウェブサイトに移動

それから
 
数式をシートに入力するとした場合、検索キーのセルと、検索結果を受取るセルが同じはあり得ないです。
>Sheets("データ追加").Cells(i, 2) = WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:A"), 1, 0)
 
↑の赤字の部分です。
VBAだから、エラーにはなりませんが。
もし、検索範囲に一致しなかったら、エラー文字が埋め込まれてしまいます。
 
この論理で考えると
キーが一致していることを確認するだけでよいのではないでしょうか?
キーが一致したら、同値で置換になるが、
一致しなかったら、どのようにしたいのでしょうか?

回答
投稿日時: 18/09/28 10:28:46
投稿者: WinArrow
投稿者のウェブサイトに移動

>Sheets("データ追加").Cells(i, 2) = WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:A"), 1, 0)
 
↑のコードは、質問時の掲示を加味すると
Sheets("データ追加").Cells(i, 7) = WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:B"), 2, 0)
という形になりますが、
どちらが本当ですか?
 
なお、このコードでエラーになるならば
>Sheets("データ追加").Cells(i, 2)
のデータ型と
>Sheets("マスタ").Range("A:A")
のデータ型が一致していないことが推測できます。
 
各々のデータ型を確認してみてください。

回答
投稿日時: 18/09/28 11:40:59
投稿者: WinArrow
投稿者のウェブサイトに移動

WinArrow さんの引用:
>Sheets("データ追加").Cells(i, 2) = WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:A"), 1, 0)
 
↑のコードは、質問時の掲示を加味すると
Sheets("データ追加").Cells(i, 7) = WorksheetFunction.VLookup(Sheets("データ追加").Cells(i, 2), Sheets("マスタ").Range("A:B"), 2, 0)
という形になりますが、
どちらが本当ですか?
 
なお、このコードでエラーになるならば
>Sheets("データ追加").Cells(i, 2)
のデータ型と
>Sheets("マスタ").Range("A:A")
のデータ型が一致していないことが推測できます。
 
各々のデータ型を確認してみてください。

↑の最後のコメントは、私の過去レス
投稿日時: 18/09/19 16:31:08
にでも、同じことを言っていますが、
確認してみたのでしょうか?
 

回答
投稿日時: 18/09/28 18:11:00
投稿者: WinArrow
投稿者のウェブサイトに移動

質問時に掲示の表は、「データ追加」シートなんですね?
Cells(i, 2)は、B2セルだから、文字列ですよね?
 
マスタシートの方は説明がないけど、
 
おそらく、検索キーが、マスタシートに存在していないものと
思います。
アンマッチの時は、どのようにしたいと考えているんでしょうか?
 

トピックに返信