VLookupで一致しなかった時のVBAでの処理
- VLookup関数を使用してデータを検索する際、一致するデータがなかった場合の処理方法について説明します。
- VLookup関数で一致するデータがなかった場合、エラーが発生します。このエラーを回避するためには、If文を使用して条件分岐し、処理を行う必要があります。
- 具体的な例として、VLookup関数を使用してデータを検索し、一致するデータがなかった場合には-1を代入する方法を紹介します。
- ベストアンサー
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 ---------------------
- yam2012
- お礼率80% (270/334)
- Excel（エクセル）
- 回答数2
- ありがとう数2
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
こんにちは 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 こうしてみては？
その他の回答 (1)
- tsubu-yuki
- ベストアンサー率46% (179/386)
意表をついて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の速度向上について
VBAでVLOOKUPの速度向上について、お知恵を貸していただきたく存じます。 以下のVLOOKUPのVBAがおそく、速くしたいです。行数は２万行ぐらいです。 何卒よろしくお願い申し上げます。 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を手動の設定はしています。
- 締切済み
- Visual Basic
- vbaにvlookupを組み込む
お世話になります。 エクセルのマクロにvlookupを組み込みたいのですが，エラーが出てしまいます。 用意したものは「顧客名簿」というシートで，A列に顧客番号（６ケタの数字），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")の部分に不具合があると思うのですが，どう対処すればいいでしょうか。 詳しい方，ご教授願います。
- ベストアンサー
- Excel（エクセル）
- VBAでVlookup機能を使うときにエラー
このコミュニティでもたびたび質問されているVLOOKUPのVBAですが 解答例で多く書かれているのが Public Sub test() Dim MyVariant As Variant MyVariant = Application.VLookup("excel", Range("A:B"), 2, False) If IsError(MyVariant) Then Debug.Print "Not Found" Else Debug.Print MyVariant End If End Sub なのですがRangeの部分にシートの情報を乗せるとエラーが出ます 例）Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False) まだまだ初心者なので何がおかしいのかわかりません よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- 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)のＶＬＯＯＬＵＰが実行され、尚且つ(2)のイベントのＥ４が値が１だった場合、Ｙ４にＨ４の値を入れたいのですが・・・。 (2)のみなら動くことは確認できましたが、(1)のｅｘｉｔ ｓｕｂ の直前に入れたり、ｅｎｄ ｉｆの前に入れたりしましたが、片方は動くが、もうひとつが動かないです。 (3)については、まったくわかりません。 マクロについて、自分でネットで勉強した程度なので、基本がわかっていないからなのでしょうが・・・。 どなたか、教えてください。 お願いします。
- ベストアンサー
- Visual Basic
- 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
- 締切済み
- その他MS Office製品
- 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
- ベストアンサー
- オフィス系ソフト
- 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の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型に入れて検索する？）、 何か良い方法はないものでございましょうか？ もしお詳しい方がいらっしゃいましたら，是非アドバイスいただきたく 何卒よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- エクセル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 VLOOKUP
お世話になります。 VLOOKUPの範囲を変数で取得したいのですが教えて下さい。 下記コードは動きますが、検索範囲の("A1:C4")の『Ｃ4』を変数に変更はできますでしょうか。 （データは増える予定ですなので） Range("E1") = Application.WorksheetFunction.VLookup(Range("D1"), Range("A1：C4"), 2, False) 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
お礼
教えていただきました方法でうまくいきました。 ありがとうございました。