• ベストアンサー
  • 困ってます

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 ---------------------

共感・応援の気持ちを伝えよう!

  • 回答数2
  • 閲覧数409
  • ありがとう数2

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

  • ベストアンサー
  • 回答No.1

こんにちは VLookupに拘らずに、 Sub test()   Dim x As Integer   Dim y As String   Dim z As Variant   x = 7   z = Application.Match(x, Worksheets("Sheet1").Range("A1:A100"), 0)   If IsError(z) Then     y = -1   Else     y = Worksheets("Sheet1").Range("B" & z)   End If End Sub こうしてみては?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

教えていただきました方法でうまくいきました。 ありがとうございました。

関連するQ&A

  • VBAのVLOOKUPの速度向上について

    VBAでVLOOKUPの速度向上について、お知恵を貸していただきたく存じます。 以下のVLOOKUPのVBAがおそく、速くしたいです。行数は2万行ぐらいです。 何卒よろしくお願い申し上げます。 Dim 範囲A As Range Set 範囲A = Worksheets("取引先").Range("A:H") On Error Resume Next myCnt5 = 2 Do Worksheets("受注データ").Cells(myCnt5, 49).Value = WorksheetFunction.VLookup(Worksheets("受注データ").Cells(myCnt5, 48), 範囲A, 6, False) myCnt5 = myCnt5 + 1 If Worksheets("受注データ").Cells(myCnt5, 1).Value < 10 Then Exit Do Loop On Error Resume Next myCnt6 = 2 Do Worksheets("受注データ").Cells(myCnt6, 51).Value = WorksheetFunction.VLookup(Worksheets("受注データ").Cells(myCnt6, 50), 範囲A, 8, False) myCnt6 = myCnt6 + 1 If Worksheets("受注データ").Cells(myCnt6, 1).Value < 10 Then Exit Do Loop On Error Resume Next myCnt7 = 2 Do Worksheets("受注データ").Cells(myCnt7, 53).Value = WorksheetFunction.VLookup(Worksheets("受注データ").Cells(myCnt7, 52), 範囲A, 6, False) myCnt7 = myCnt7 + 1 If Worksheets("受注データ").Cells(myCnt7, 1).Value < 10 Then Exit Do Loop 補足 上記VBAには記載していませんが、Application.ScreenUpdatingの停止、Application.Calculationを手動の設定はしています。

  • VBAでVLOOKUP関数を使う

    「在庫検索」に下記条件を追加するには、どうすれば良いのでしょうか。 1)G列が1500より大きければ Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 2, False) 2)G列が1500より小さければ Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 3, False) どちらの際も空白の条件、If ActiveCell.offset(i, 1).value = ""は残ります。 1)、2)とandを組み合わせる方法でチャレンジしたのですが、出来ませんでした。 ------------------------------------------------------------------------ 以下がベースの「在庫検索」です。 一度、質問して解決したのですが、更なる問題が発生してしまいました。 ご指導ください。 ------------------------------------------------------------------------ Sub 在庫数検索() Dim SerchName As String Dim SerchArea As Range Dim Results As Variant '初期設定 Range("A2").Activate ItemCode = Range("A2").Value i = 0 '検索範囲の設定(ポイント1) Set SerchArea =Worksheets("シート2").Range("List1") '商品コードが空になったら終わり Do Until ItemCode = "" If ActiveCell.offset(i, 1).value = "" Then '★1 On Error Resume Next ItemCode = ActiveCell.offset(i, 0).value Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 2, False) If Err <> 0 Then Results = "" ActiveCell.offset(i, 1) = Results End If '★1 i = i + 1 Loop

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

その他の回答 (1)

  • 回答No.2

意表をついてCOUNTIFとか Sub sample()     x = 1     If WorksheetFunction.CountIf(Range("A1:A100"), x) = 1 Then         y = WorksheetFunction.VLookup(x, Range("A1:B100"), 2, False)     Else         y = -1     End If     Debug.Print x, y End Sub

共感・感謝の気持ちを伝えよう!

質問者からのお礼

こちらの方法でもうまくいきました。 ありがとうございました。

関連するQ&A

  • エクセル VBA VLOOKUP

    お世話になります。 VLOOKUPの範囲を変数で取得したいのですが教えて下さい。 下記コードは動きますが、検索範囲の("A1:C4")の『C4』を変数に変更はできますでしょうか。 (データは増える予定ですなので) Range("E1") = Application.WorksheetFunction.VLookup(Range("D1"), Range("A1:C4"), 2, False) 宜しくお願い致します。

  • Vlookupマクロにつきまして

    初心者で、 エクセルのマクロにて、下記の処理を行おうとしているのですが、 上手く行きません。 どなたかお助けください! B3=Vlookup(A3,Sheet2!A:F,3,False) B4 A4 B5 A5 とA列に値がある限りカウントアップしていきます。 以下の通り考えてみたのですが、動きません。 Sub vlookup() Dim i As Long For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row Cells(i, "B") = Application.WorksheetFunction.VLookup(Range(Cells(i, "A")), Worksheets("Sheet2").Range("A:F"), 3, False) Next 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の勉強をしております。 別のファイルからVLOOKUPで値を参照したいのですが、 範囲を指定してみましたが、 「worksheetFunctionクラスのVlookupプロパティを参照できません」 とのメッセージが出てしまいます。 なお、値は空白になる行もありますので、 if関数で回避してみましたがうまくいきません。 いろいろ試しましたが、何度やってもうまくいかないので こちらに質問させていただきました。 お詳しい方、ご伝授いただければ助かります。 よろしくお願い致します。 環境はExcel2007です。 Sub sample() Dim 範囲 As Range Dim wb As Workbook, wb2 As Workbook Dim r As Integer,intRow As Integer Workbooks.Open Filename:="***.xlsm" Set wb = ThisWorkbook Set wb2 = ActiveWorkbook Set 範囲 = wb2.Sheets("PvtSht2").Range("Database3") r = wb.Sheets("sheet1").Range("A28:N28").End(xlToRight).ColumnintRow = 3 With wb.Sheets("sheet1") Do Until .Cells(intRow, 1).Value = "" .Cells(intRow, (r + 1)) = Application.WorksheetFunction.If((Application.WorksheetFunction.VLookup(Cells(intRow, 1), 範囲, 2, False)) = 0, "", Application.WorksheetFunction.VLookup(Cells(intRow, 1), 範囲, 2, False)) intRow = intRow + 1 Loop End With 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

  • 日付セルを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 WorksheetFunction.VLookupとoffsetプロパティー

    エクセル2002使用です。 VBAの記述で、WorksheetFunction.VLookupを使って求めた値の入っているセルの列方向に一つ下の値を取得したいのですが・・・・ range("A1").Value =WorksheetFunction.VLookup( range("B1").Value, Range("A3:C10"), 3, 1) 例えば上記で得た値がC4セルにあるのであれば、C5セルの値を取得したいのです。 offset(0,1)プロパティーをどこにおけば良いのか良くわかりません。 どうかよろしくお願いします。

  • 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

  • InputBoxの値で検索して転記するマクロ

    1.InputBoxを3回表示させます。 2.ユーザーに入力してもらいます。 入力できるのは半角英数字のみでそれ以外は エラーメッセージを出したいです。 3.1回目は18桁か22桁以外の場合、 2回目と3回目は4桁以外の場合はMsgBoxを表示して 再入力を促します。 4.InputBoxに入力された値の3個を連結した値で Sheet2のA列を検索して 合致したらその行のG列の値を見ます。 5.G列に"済"とあったら MsgBoxを表示して 中止するか継続するか判断します。 6.継続した場合 その該当行の各列の値をSheet1にそれぞれ転記します。 Sheet2の該当行のB列→Sheet1のセルB3 Sheet2の該当行のC列→Sheet1のセルC3 Sheet2の該当行のD列→Sheet1のセルD3 Sheet2の該当行のE列→Sheet1のセルE3 Sheet2の該当行のF列→Sheet1のセルF3 7.かつSheet2の該当行のG列に 済 と転記します。 すでに済が記入されている場合は上書です。 以下のように作成しましたがエラーで動かなくて動作確認が出来ません。 どう直せばいいのか教えてください。 イレギュラー時の対応処理が必要だとも思うのですが動作しない為 思いつきません。 記述が滅茶苦茶なのですがこれが限界です。申し訳ありません。 Sub 表示板作成() Dim 検索値1 Dim 検索値2 Dim 検索値3 Dim 検索値4 Dim 判定値 Dim 判断 Dim 記録 Dim 確認 検索値4 = 検索値1&検索値2&検索値3 Do 検索値1 = Application.InputBox("型番を入力してください") If Len(検索値) < 18 Then MsgBox "18桁未満です。再入力しますか?" Loop Else Exit Do '検索値2と3も上記と同じ記述をここへ入れる '(現在省略) End If 判定値 = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 7, 0) If 判定値 = "済" Then 判断 = MsgBox("発行済みです。再度データ取得しますか?", vbYesNo) Else Select Case 判断 Case vbNo Exit Sub Case vbYes Range("B3").Value = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 2, 0) Range("B4").Value = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 3, 0) Range("B5").Value = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 4, 0) Range("B6").Value = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 5, 0) Range("B7").Value = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 6, 0) End Select End If 記録 = Application.WorksheetFunction.VLookup(検索値4.Value, Worksheets("Sheet2").Range("A2:G10000"), 7, 0) 記録.Value = "済" 確認 = MsgBox("これは●●用です。いいですか?", vbYesNo) Select Case 確認 Case vbNo Exit Sub Case vbYes Call 印刷 End Select End Sub

  • VBAでVLOOKUPを使う際に、別のシートの範囲を参照する方法

    はじめまして、saitou takayukiです。 vbaでデータの処理を行っているのですが、 その際、vlookup関数を使う際に問題がありました。 プログラミングの内容はsheet1で指定した範囲の10列目のデータを vlookup関数で参照し、sheet2のデータへ書き込むのですが、 vbaでvlookup関数がうまく動いてくれません。 ちなみにsheet1で指定した範囲はユーザーフォームを利用しています。 具体的な中身は Application.Worksheets(2).Cells(10,10) = Application.worksheetFunction.VLookup(id_number,hani_range ,10, 0) としていますが、エラーがでて動きません。 hani_rengeが参照範囲なのですが、これは ユーザーフォームで範囲指定することで得たテキスト形式のhani_textをRenge形のhani_range変数に変えたものです。 アドバイスお待ちしております。よろしくお願いします。