VBAにVLOOKUPを組み込む方法とエラー解決法

このQ&Aのポイント
  • エクセルのマクロにVLOOKUP関数を組み込む方法について詳しく教えてください。
  • VLOOKUP関数において、「WorksheetFunctionクラスのVLookupプロパティを取得できません」というエラーが出る場合の対処法を教えてください。
  • VLOOKUP関数での範囲指定に関する注意点を教えてください。
回答を見る
  • ベストアンサー

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")の部分に不具合があると思うのですが,どう対処すればいいでしょうか。 詳しい方,ご教授願います。

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

  • ベストアンサー
  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.1

 原因のまず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

mine2309
質問者

お礼

さっそくご教授いただきありがとうございます。 Stringでは文字列扱いになるなど,大変わかりやすく教えていただきありがとうございます。 顧客データの番号は,6ケタの数字のみなので,IntegerとType:=1で試してみましたがうまくいきませんでした。が,Variantに書き換えてType:=3で試してみたところ,うまくいきました。 ちょっと不思議ですが,結果的にはこちらが望む通りのものができました。 今後ともよろしくお願いします。

関連するQ&A

  • VLookupで一致しなかった時のVBAでの処理

    On Error ~を使わないで、 VLookup()で一致しなかった時の処理をさせたいのですが どのように記述すればよいでしょうか。 例えば、以下のようなコードの場合、 一致したデータがない時にyに-1を代入するには 以下のコードをどのように記述すればよいのでしょうか。 --------------------- Dim x As Integer Dim y As String x = 7 y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) --------------------- 以下はいずれもエラーになりますが、以下のような感じで処理がしたいです。 --------------------- If IsError(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False)) Then  y = -1 Else  y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) End If --------------------- If Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False)) Then  y = -1 Else  y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) End If --------------------- y = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False), -1) --------------------- なお、以下のように本来エラーではない処理で On Error Resume Nextを使うのは、 本当のエラーの処理と混同するため不可 --------------------- On Error Resume Next y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) If Err <> 0 Then y = -1 On Error GoTo 0 ---------------------

  • 日付セルをVBAのVLOOKUPで見つけられません

     今日は、質問させていただきます。どうぞよろしくお願いいたします。  環境:Excel2010+Win7 でございます。  今、A列の日時データの中から、特定の日時のセルの行を取得しようといたしまして、下記のようにコーディングいたしました。が何故か WorksheetFunction.CountIfでは見つかっているのですが WorksheetFunction.VLookupではエラーが帰ってまいります。 【A列のデータ】(ユーザ定義で「yyyy/mm/dd hh:mm:ss.000」としております。) 2015/05/16 22:00:00.002 2015/05/16 22:00:00.182 2015/05/16 22:00:00.342     :     : 【VBA】 Sub test()  Dim WS_DATA As Worksheet  Set WS_DATA = ThisWorkbook.Worksheets("Sheet1")  With WS_DATA   str時間 = "2015/05/16 22:00:00.002"   If WorksheetFunction.CountIf(.Range("A:A"), str時間) > 0 Then    MsgBox (Application.WorksheetFunction.VLookup(str時間, .Range("A:A"), 1, False)) '←ここでエラーになります   Else  MsgBox str時間 & "がリストに存在しません。"   End If  End With End Sub 【実行結果】  コード中に記しましたエラー箇所で、 「実行時エラー1004   WorksheetFunctionクラスのVlookupプロパティーを取得できません」となります。検索範囲内に見つからない場合のエラーのようでございます。  あとVBAに 日時 = Format(str時間, "yyyy/mm/dd hh:mm:ss.000") を追記して、この「日時」を探してみましたが、同じ結果になりました。。。 (CountIfでは見つかっていますが、Vlookupではエラーになります。)  データが大量にありますので高速に検索出来る方法を探しておりますが (=おそらく、VLookUpを使うか、もしくはA列をVariant型に入れて検索する?)、 何か良い方法はないものでございましょうか?  もしお詳しい方がいらっしゃいましたら,是非アドバイスいただきたく 何卒よろしくお願いいたします。

  • エクセルのVBA

    お世話になります。 ただ今,VBAで以下のことに挑戦していますが,行き詰ってしまいました。皆様のお知恵をお貸しください。 1.エクセルで,ある図形をクリックすると,インプットボックスが開き,「顧客番号を入力」と出ます。 2.顧客番号を入力すると,「顧客管理」というシートのD1セルにその番号が入力されます。 3.その顧客番号をもとに「顧客名簿」というシートのデータから,その顧客の住所や性別などのデータがvlookupで引っ張ってこられます。 そこでご相談です。上記の2の操作で,顧客番号を入力後,すぐにD1セルに番号が入力されるのではなく, メッセージボックスでの確認画面「○○さんのデータを表示しますか」(○○には顧客番号に該当する顧客名が入ります)というものを表示させ,OKを押せば,D1セルに番号が入力され,キャンセルを押せば,単純に,メッセージボックスが閉じる という機能を追加したいのですが。ちなみに,現在のVBAは以下の通りです。 Dim ans As String ans = InputBox("顧客番号を入力", "入力", "") If ans <> "" Then Sheets("顧客管理").Select Range("D1").Value = ans End If End Sub これの,どこにどのようなプログラムを追加したらいいのでしょうか。 よろしくお願いします。

  • VBA CHANGEイベントに複数イベントを

    いつもお世話になっています。 色々しらべて試してみたんですが、うまくいかないんで教えてください。 CHANGEイベントに複数のイベントを書き込みたいんですが。 今現在、問題なく動いている以下のイベントがあります。 (1) Private Sub Worksheet_Change(ByVal Target As Range) Dim rang3 As Range Dim rang4 As Range Dim ■■ As String Dim LastRow1 As Long LastRow1 = Worksheets("○○").Cells(Rows.Count, "b").End(xlUp).Row Set rang4 = Worksheets("○○").Range("b:I" & LastRow) Set rang3 = Range("h4") If Intersect(Target, rang3) Is Nothing Then Exit Sub On Error Resume Next ■■ = WorksheetFunction.VLookup(Target.Value, rang4, 2, 0) If Err.Number > 0 Then MsgBox Target.Value & "はありません。基本情報台帳に入力してください。" Range("h4").Select Else Application.EnableEvents = False Range("I4").Value = Application.WorksheetFunction.VLookup(Target, Worksheets("△△").Range("b:I"), 2, False) Range("j4").Value = Application.WorksheetFunction.VLookup(Target, Worksheets("△△").Range("b:I"), 3, False) Range("k4").Value = Application.WorksheetFunction.VLookup(Target, Worksheets("△△").Range("b:I"), 7, False) Range("l4").Value = Application.WorksheetFunction.VLookup(Target, Worksheets("△△").Range("b:I"), 8, False) Range("m4").Value = Application.WorksheetFunction.VLookup(Target, Worksheets("△△").Range("b:I"), 5, False) Application.EnableEvents = True Range("K4").Select End If End Sub このシートにもう一つ、イベントを入れたいのですが。 (2) If Target.Count > 1 Then Exit Sub If Intersect(Target, Range("E4")) Is Nothing Then Exit Sub Else If Range("e4").Value = "1" Then Target.Offset(0, 19).Value = "☆" End If どこに入れればいいのかわかりません。 (3) また、(2)のイベントの他に、 (1)のVLOOLUPが実行され、尚且つ(2)のイベントのE4が値が1だった場合、Y4にH4の値を入れたいのですが・・・。 (2)のみなら動くことは確認できましたが、(1)のexit sub の直前に入れたり、end ifの前に入れたりしましたが、片方は動くが、もうひとつが動かないです。 (3)については、まったくわかりません。 マクロについて、自分でネットで勉強した程度なので、基本がわかっていないからなのでしょうが・・・。 どなたか、教えてください。 お願いします。

  • エクセルVBA VLOOKUPについて

    エクセル VBA初心者です。 関数でのVLOOKUPをVBAで作りたいのですが、上手くいきません。 あらかじめ、Sheet2の1から300行までに A列  / B列 商品名 / 商品コード が入力されています。(名前の定義=商品コード) Sheet1にユーザーフォームを利用して、データを書き込んだ後、 B列に商品名が書き込まれると、 A列に商品コードが表示されるようにしたいと考えています。 A列に =IF(B2="","",VLOOKUP(B2,商品コード,2,FALSE)) と入力していたのですが、 VBAでIfを使って出来ないかと考えてみたのですが、 上手くいきませんでした。 Private Sub Worksheet_Change(ByVal Target As Range) Dim sRow As Long Dim sColumn As Long sRow = ActiveCell.Row sColumn = ActiveCell.Column If Cells(sRow, 2).Value = True Then Cells(sRow, 1).Value = WorksheetFunction.VLookup(Cells(sRow2).Value, Worksheets("Sheet2").Range("A1:B300"), 2, False) ElseIf Cells(sRow, 2).Value = " " Then Cells(sRow, 1).Value = " " End If End Sub ご教授いただけないでしょうか? エクセル2003 WindowsXP

  • エクセルでフォームを作成しています。(VBA)

    エクセルでフォームを作成しています。(VBA) フォームAで作成してあるリストボックスから選択して 取得した番号を フォームBにもってきて、その番号をもとにVLOOKUPで ほかのテキストボックスに値を持ってこようとしている のですがうまくいきません。 フォームAのリストボックスにはsheet1の名簿が 表示されています。 クリックで選択した名簿番号を取得して、フォームBにある 個人のページでその他の詳細を出そうとしています。 名簿番号はフォームBの名簿番号.Textに持ってこれたのですが その名簿番号をVLOOKUPで研究名を出そうとしてもうまくいきません。 コードのどこがだめなのか分かりません。 VBA入門者です。 よろしくお願いいたします。 --------------------- Private Sub 決定_Click() Dim ADR As Variant On Error GoTo ExitER Set ADR = Worksheets("sheet1").Range("A:F") 研究名.Text = Application.WorksheetFunction.VLookup(名簿番号.Text, ADR, 4, False) On Error GoTo 0 Exit Sub ExitER: MsgBox "一致するコードがありません" On Error GoTo 0 End Sub ---------------------

  • VBAでVlookup関数を組もうとしていますがエラーが出ます。VBAに詳しい方、教えてください

    VBAでvlookup関数を下のように組みましたが、(1)でエラーが出ます。VBAに詳しい方、教えてください。 Sub VLLOKUPによる表の検索4() Dim mykensakuchi Dim mykensakuhan Dim gyo As Integer (1) mykensakuchi = Worksheets("sheet1").Range("a" & gyo).Value mykensakuhan = Worksheets("sheet2").Range("b2:e9") saikagyo = Worksheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row gyo = 2 For gyo = saikagyo To 1 Step -1 With Application.WorksheetFunction Range("b:gyo").Value = .VLookup(mykensakuchi, mykensakuhan, 2, False) End With Next End Sub

  • マクロをすっきりさせたい・・・

    いつもお世話になっております。 下記、マクロを組んだのですが、 簡潔にまとめるには、どうしたら良いでしょうか・・・ 宜しくお願い致します。 Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000") ThisWorkbook.Activate 列番号 = 7 検索値 = (Worksheets("B").Range("B24")) Range("D14").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000") ThisWorkbook.Activate 列番号 = 7 検索値 = (Worksheets("B").Range("B25")) Range("D15").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000") ThisWorkbook.Activate 列番号 = 7 検索値 = (Worksheets("B").Range("B26")) Range("D16").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)

  • VBA VLookupを使用時のエラーの解消方法を教えてください。

    ダイアログボックス(ユーザフォームで作成)で入力し、得意先台帳を作成しています。 入力したデータの項目に変更(例えば社名変更)があった場合、修正用のダイアログボックスを呼出し表示させ、修正をしたいので下記のように作成しました。 実行するとエラーになります。 エラーメッセージ 実行時エラー 1004 WorksheetFunctionクラスのVLookupプロパティを取得できません 教えていただきたき事。 1.エラーの解消方法 2.下記記述の誤謬 Private Sub koudo_afterUpdate() Dim WS1 As Worksheet Set WS1 = Worksheets("得意先登録") Dim CODE As Long If WorksheetFunction.CountIf(WS1.Range("D:D"), koudo.Value) = 0 Then MsgBox "得意先コード未登録。" koudo.SetFocus Exit Sub Else If IsNumeric(koudo.Text) = True Then CODE = Val(koudo.Text) Else CODE = koudo.Text End If tourokubi.Text = Application.WorksheetFunction.VLookup(CODE, WS1.Range("A:S"), 3, False)     ・・・・・・・・・・ jouken.Text = WorksheetFunction.VLookup(CODE, WS1.Range("A:S"), 18, False) End If End Sub

  • VBA changeイベントを複数入れたい

    VBA changeイベントを複数入れたい VBAは初心者で、以前もこちらでお世話になりました。 F4セルに入力した際、VLOOKUPで検索し、該当がなければメッセージボックスを出し、 該当があれば、そのまま次に進む、というchangeイベントが既にあります。 ここに、E4セルに入力した数字が、同じシートのE列5行目以下と重複していた場合、 エラーメッセージを出す、とのをつけたしたいと思っています。 IFを使えばいい、ということはわかるのですが、どこに入れたらいいのかがわからず・・・。 すでにあるVBAは以下のとおりです。 Private Sub Worksheet_Change(ByVal Target As Range) '処分受託者(入力用名称)を入力して、処分業者名簿になければエラーメッセージを出す。 Dim rang1 As Range Dim rang2 As Range Dim 処分受託者名称 As String Dim LastRow As Long LastRow = Worksheets("処分業者名簿").Cells(Rows.Count, "b").End(xlUp).Row Set rang2 = Worksheets("処分業者名簿").Range("b4:b" & LastRow) Set rang1 = Range("f4") If Intersect(Target, rang1) Is Nothing Then Exit Sub On Error Resume Next 処分受託者名称 = WorksheetFunction.VLookup(Target.Value, rang2, 1, 0) If Err.Number > 0 Then MsgBox Target.Value & " はありません" Range("f4").Select Else End If End Sub この、どこに重複の場合はエラーメッセージを出す、というのを入れればいいのか、 教えてください・・・。

専門家に質問してみよう