こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

vbaにvlookupを組み込む

お世話になります。

エクセルのマクロにvlookupを組み込みたいのですが,エラーが出てしまいます。

用意したものは「顧客名簿」というシートで,A列に顧客番号(6ケタの数字),B列に顧客名が入っています。C列以降はその他のデータが入っています。

「トップメニュー」というシートにあるマクロボタン「顧客抽出」を押すと,インプットボックスが現れます。これに顧客番号を入力すると,メッセージボックスが現れます。

この時のメッセージボックスに,インプットボックスに入力された番号により「顧客名簿」から顧客名を取り出し,「○○さんのデータを表示しますか」としたいのですが,ここでエラーが出ます。(○○は,「顧客名簿」のB列にある名前です)

実行時エラー 1004 WorksheetFunctionクラスのVLookupプロパティを取得できません。

組んであるマクロは以下の通りです。

Sub 顧客抽出()
Dim ans As String, ans2 As String
    ans = InputBox("顧客番号を入力", "入力", "")
        If ans = "" Then
            MsgBox "顧客番号が空白です" & vbNewLine & _
               "処理を中止します", vbOKOnly
        Else
            If WorksheetFunction.CountIf(Worksheets("顧客名簿").Range("A1:B5000"), ans) = 0 Then
                MsgBox "顧客番号が登録されていません", vbOKOnly
            Else
                ans2 = WorksheetFunction.VLookup(ans, Worksheets("顧客名簿").Range("A1:B5000"), 2, False)
                If MsgBox(ans2 & " さんのデータを表示しますか", vbYesNo) = vbYes Then
                    Sheets("顧客情報").Select
                    Range("D4").Value = ans
                Else
                    MsgBox "処理を中止します", vbOKOnly
                End If
            End If
        End If
End Sub

途中にある ans2 = WorksheetFunction.VLookup(ans, Worksheets("顧客名簿").Range("A1:B5000"), 2, False) の部分でエラーが出るのですが,解決策が見当たりません。

おそらくRange("A1:B5000")の部分に不具合があると思うのですが,どう対処すればいいでしょうか。

詳しい方,ご教授願います。

投稿日時 - 2013-11-27 15:52:28

QNo.8364051

困ってます

質問者が選んだベストアンサー

 原因のまず1つ目は、顧客番号を入力する際に使用しているInputBoxメソッドの所で、入力可能なデータ型を指定していないため、数値を入力しても数値データではなく、文字列データに変換されてしまうためです。
 顧客名簿のA列には、6桁の数値データが入力されているのであって、6個の数字から成る文字列データが入力されている訳ではありませんから、Vlookup関数の検索値に6個の数字から成る文字列データを指定した処で、顧客名簿のA列の中には検索値と同じデータが見つからないためエラーとなる訳です。
 原因の2つ目は、顧客番号を入力する際に使用しているInputBoxメソッドでデータを取得するための変数ansを定義する際に、As Stringで定義しているため、変数ansのデータ型が文字列データとなってしまい、その中に数値データを格納しようとしても、文字列データに変換されてしまいますから、Vlookup関数の検索値にansを指定しますと、文字列データを検索する事になり、数値データしか入力されていない顧客名簿のA列の中には検索値と同じデータが見つからないためエラーとなる訳です。

 ですから、顧客番号の中に、例えば「123456」の様に完全な数値として扱う事が出来るものと、例えば「10A235」等の様に数字以外の文字が含まれているものの両方が混在している場合には、

Dim ans As String

の部分を

Dim ans As Variant

に変更した上で、

ans = InputBox("顧客番号を入力", "入力", "")

という箇所も

ans = Application.InputBox(Title:="入力", Prompt:="顧客番号を入力", Type:=3)

等の様に変更されると良いと思います。


 因みに、顧客番号が必ず6桁の数値データとなっている場合には、

Dim ans As Integer



ans = Application.InputBox(Title:="入力", Prompt:="顧客番号を入力", Type:=1)

の様にします。


【参考URL】
 よねさんのWordとExcelの小部屋 > Excel(エクセル)講座の総目次 > Excel(エクセル) VBA入門:目次 > 対話型処理2(InputBox関数,InputBoxメソッド)
  http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_inputbox.html

 Excelでお仕事! > VBA基本 > 変数のデータ型
  http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_050_06.html

投稿日時 - 2013-11-27 17:32:14

お礼

さっそくご教授いただきありがとうございます。

Stringでは文字列扱いになるなど,大変わかりやすく教えていただきありがとうございます。

顧客データの番号は,6ケタの数字のみなので,IntegerとType:=1で試してみましたがうまくいきませんでした。が,Variantに書き換えてType:=3で試してみたところ,うまくいきました。

ちょっと不思議ですが,結果的にはこちらが望む通りのものができました。

今後ともよろしくお願いします。

投稿日時 - 2013-11-27 18:24:15

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(1)

あなたにオススメの質問