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

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) まだまだ初心者なので何がおかしいのかわかりません よろしくお願いします

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

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

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

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

私もVBAでVLOOKUPは一般的ではないと思いますよ VBA的にやるとこんな感じかな Private Sub データ表示() Dim a As Range Label日付.Caption = Sheets("休暇・特勤").Cells(CurRow, 1) Label曜日.Caption = Format(Sheets("休暇・特勤").Cells(CurRow, 1), "(aaa)") Set a = Sheets("祝日").Range("B4:B34").Find(Sheets("休暇・特勤").Cells(CurRow, 1).Value) If a Is Nothing Then Label休日.Visible = False Else Label休日.Visible = True End If End Sub 試していないので、エラー出るかも

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

質問者からのお礼

ありがとうございました。曜日の部分もスマートな形に訂正していただき感謝です。 マクロ、VBAを始めてほんのちょっと慣れてきましたが、知っている知識のみで処理をさせようとして無理が出たみたいです。 今後も精進していきます。

関連するQ&A

  • Vlookup関数で行と列を両方Loopで回したい

    エクセル2010です。 Vlookup関数を使って、下記のようなコードを作りました。 行のLoopはできたのですが、列がわからなくて 1列ごと、50列まで書きました。 列のLoopはどうすればいいのでしょうか? よろしくお願いいたします。 Sub 毎月集計() Dim i As Byte Dim 範囲 As Range Dim myV As Variant Sheets("実績").Select Set 範囲 = Worksheets("2月").Range("B7:AZ20") For i = Application.InputBox("開始行を半角で入力してください。", Default:=123, Type:=1) To Application.InputBox( _ "最終行を半角で入力してください。", Default:=123, Type:=1) myV = Application.VLookup(Range("B" & i).Value, 範囲, 2, False) If IsError(myV) Then                Range("C" & i).Value = "0"         Else Range("C" & i).Value = myV End If myV = Application.VLookup(Range("B" & i).Value, 範囲, 3, False) If IsError(myV) Then Range("D" & i).Value = "0" Else Range("D" & i).Value = myV End If 以下省略 Next i End Sub

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

    On Error &#65374;を使わないで、 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 ---------------------

  • VLOOKUPのエラーについて

    教えて下さい。 =IF(ISERROR(VLOOKUP($B3,Sheet2!$A$2:$D$35,4,FALSE)),"",VLOOKUP($B3,Sheet2!$A$2:$D$35,4,FALSE))+IF(ISERROR(VLOOKUP($B3,Sheet3!$A$2:$C$50,3,FALSE)),"",VLOOKUP($B3,Sheet3!$A$2:$C$50,3,FALSE)) という数式を使っています。 最初のIF文(Shee2)については有効なのですが、それならと、欲張って他のシートも参照できるように、+の後にIF文(Sheet3)を追加したら#VALUE!が表示されます。 何がいけないのでしょうか?

その他の回答 (2)

  • 回答No.2
  • Wendy02
  • ベストアンサー率57% (3570/6232)

こんにちは。 >コミュニティでもたびたび質問されているVLOOKUPのVBAですが たびたび? VBAのコードとしては、VLOOKUPは、ほとんど使わないと思います。 >Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False) VBAとワークシートでは、Range オブジェクトの考え方が違います。必要な大きさだけ取ればよいのです。詳しい説明は混乱しかねませんから、割愛しますが、VBAはワークシートの延長ではありませんから、とてもイレギュラーです。 それと、 Label6.Caption って、どこのラベルなのでしょうか。これを、そのまま置いたところで、該当シートモジュール以外はエラーが発生するはずです。こういう場合、親オブジェクトから正しく書かなくてはなりません。言い換えると、コンテナを使わなくてはなりません。しかし、それより以前に、Label ではなく、TextBox にしてほしいものですね。 それと、Public キーワードを付けているか、分かっていればよいけれども、その書く意義が分からないなら、書かないほうがよいです。 後は、文字列か数値かということですが、それは、#1さんの回答に譲ります。

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

質問者からのお礼

厳しい御指摘ありがとうございます。 ほかのコミュニティでもVBAに慣れてきた初心者の多い行動に 『すべてをVBAで処理させようとする』をあげておられました。 まさに今の私自身でした(汗) 指摘通り、Public キーワードを書く意義もわかっていませんでした。 Sheets("○")とWWorkSheets("○")の違いも今日知りました また初心に帰って基礎を勉強します

  • 回答No.1
  • n-jun
  • ベストアンサー率33% (959/2870)

>例)Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False) このコードの前に MsgBox TypeName(Label6.Caption) を追加してみてStringと出たとします。 検索値が”数値”であった場合は「型が一致しない」となるかと。 そこで”数値”にしてあげるためVal関数を用いる。 MsgBox TypeName(Val(Label6.Caption))・・・Doubleになる。 Application.VLookup(Val(Label6.Caption), WorkSheets("Sheet5").Range("A:B"), 2, False) と言う事ではないでしょうか。

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

質問者からの補足

ありがとうございます 下記のものが実際のコードです Private Sub データ表示() Label日付.Caption = Sheets("休暇・特勤").Cells(CurRow, 1) 曜日 = Weekday(Label日付.Caption) Select Case 曜日 Case vbSunday: Label曜日.Caption = "(日)" Case vbMonday: Label曜日.Caption = "(月)" Case vbTuesday: Label曜日.Caption = "(火)" Case vbWednesday: Label曜日.Caption = "(水)" Case vbThursday: Label曜日.Caption = "(木)" Case vbFriday: Label曜日.Caption = "(金)" Case vbSaturday: Label曜日.Caption = "(土)" End Select If IsError(Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False)) Then ElseIf Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False) = False Then Label休日.Visible = False ElseIf Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False) = True Then Label休日.Visible = True End If End Sub 祝日シートのB列に祝日の日付(1/1など)D列にTRUEと表記しています ユーザーフォーム上でLabel日付.Captionに表示された日付になると『祝日です』と表記されるVBAを作ろうとしています。 しかし確認用に Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False)の値を通常シートに表記すると全日付で『#N/A』となってしまいます? ワークシート上で関数を使ってユーザーフォームに値を返したほうが無難ですかね?

関連するQ&A

  • マクロを動かすとき、毎回シート名を変更したい

    マクロを登録しているBOOKに毎月前月の名前のシートを作成し、システムからダウンロードしたデーターを張り付けます。 そのデーターをVlookup関数で検索し「実績」のシートに、値を張り付けしています。 範囲のシート名が「2月」、「3月」と毎月変更になるので、インプットボックス?で変更できるようなコードを教えてください。 検索してできた初心者のコードですので、もっとスマートなコードがありましたら教えてください。よろしくお願いいたします。 エクセル2010を使用しています。 以下コード Sub 毎月集計() Dim i As Byte Dim 範囲 As Range Dim myV As Variant Sheets("実績").Select Set 範囲 = Worksheets("2月").Range("B7:AZ20")←ここをインプットボックスで変更したい For i = Application.InputBox("開始行を半角で入力してください。", Default:=123, Type:=1) To Application.InputBox( _ "最終行を半角で入力してください。", Default:=123, Type:=1) myV = Application.VLookup(Range("B" & i).Value, 範囲, 2, False) If IsError(myV) Then Range("C" & i).Value = "0" Else Range("C" & i).Value = myV End If myV = Application.VLookup(Range("B" & i).Value, 範囲, 3, False) If IsError(myV) Then Range("D" & i).Value = "0" Else Range("D" & i).Value = myV End If myV = Application.VLookup(Range("B" & i).Value, 範囲, 4, False) If IsError(myV) Then Range("E" & i).Value = "0" Else Range("E" & i).Value = myV End If myV = Application.VLookup(Range("B" & i).Value, 範囲, 5, False) If IsError(myV) Then Range("F" & i).Value = "0" Else Range("F" & i).Value = myV End If ’以下51列まで続く  Next i End Sub

  • 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関数を組もうとしていますがエラーが出ます。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関数とテキストボックスの連携

    エクセルでユーザーフォームを作成しています。 「テキストボックス11」に、以下のように、VLOOKUP関数を入れたいのですが、 どのようにしたら良いのか、わかりません。 ●入れたい関数は ISERROR関数とVLOOKUP関数を組み合わせたもの。 =if(iserror(vlookup(「コンボボックス1」の値,マスタ!A80:D90,4,false)),""vlookup(「コンボボックス1」の値,マスタ!A80:D90,4,false)) (1)「コンボボックス1」の値を検索値とする。 (2)参照範囲は「マスタ」シートの「A80:D90」 失敗しているVBAは以下の通りです。 Private Sub UserForm_Initialize() TextBox11.Value = Application.WorksheetFunction.if(iserror(VLookup(ComboBox1.Value, Range("A85:D94"), 4)),"",VLookup(ComboBox1.Value, Range("A85:D94"), 4)) 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を使用時のエラーの解消方法を教えてください。

    ダイアログボックス(ユーザフォームで作成)で入力し、得意先台帳を作成しています。 入力したデータの項目に変更(例えば社名変更)があった場合、修正用のダイアログボックスを呼出し表示させ、修正をしたいので下記のように作成しました。 実行するとエラーになります。 エラーメッセージ 実行時エラー 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イベントに複数イベントを

    いつもお世話になっています。 色々しらべて試してみたんですが、うまくいかないんで教えてください。 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 Changeイベントのエラー

    エクセルで簡単な計算書を作成しています。(マクロ初心者) ちなみにこのコードは自分で作成したものではなく、人から聞いていじってみました。 Private Sub Worksheet_Change(ByVal Target As Range) '一度に複数セルの値が変更された場合は終了 '(A5:C5を選択しDeleteも含みます。) If Target.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("H170:K170", "H171:K171","C76")) Is Nothing Then Exit Sub Application.EnableEvents = False '数値かつ空白以外の場合 If IsNumeric(Target.Value) And Target.Value <> "" Then Me.Range("M170").Formula = "=if(iserror(H170*I170*J170*K170),""-"",H170*I170*J170*K170)" '空白の場合 ElseIf Target.Value = "" Then Me.Range("H170:K170,M170").Value = "-" End If Application.EnableEvents = True Application.EnableEvents = False '数値かつ空白以外の場合 If IsNumeric(Target.Value) And Target.Value <> "" Then Me.Range("M171").Formula = "=if(iserror(H171*I171*J171*K171),""-"",H171*I171*J171*K171)" '空白の場合 ElseIf Target.Value = "" Then Me.Range("H171:K171,M171").Value = "-" End If Application.EnableEvents = True Application.EnableEvents = False '空白の場合 If Target.Value = "" Then Me.Range("D76:K76","C76").Value = "-" End If Application.EnableEvents = True End Sub H170、I170、J170、K170のどれかに数値の入力があった場合、M170に計算式を入力。 H170、I170、J170、K170のどれかの値をDELETEキーでクリアした場合、H170、I170、J170、K170、M170に"-"を入力。 その他に似たような処理がたくさん出てくるので、H171の処理とC76をDELETEキーでクリアした場合の処理を自分で考えて作ってみたのですが、うまく実行されません。H171~の処理はうまくいったので単純にコードをどんどん追加していけば動くと思ったんですが、いろいろ調べてもどうも方法がわからず進みません・・・ 解決してもらえるでしょうか・・

  • エクセルVBAで、VLOOKUP関数を使うとエラーが出る

    Excel97を使用しています。 ユーザーフォームを作っています。 下記のコードで、課名に対応した部名を表示させようとしたのですが、 「WorksheetFunctionクラスのVlookupプロパティを取得できません」 と出てきてしまいます。 Private Sub CB_課名_AfterUpdate()  LB_部名.Caption = Application.WorksheetFunction.VLookup(CB_課名, "部", 2, False) End Sub CB_課名はコンボボックス、LB_部名はラベル、 "部"は名前を付けたセル範囲です。その1列目はCB_課名のRowSourceで、2列目が部名です。 VBAでVlookup関数を使ったコードを公開されているサイトがありましたので、使えないことはないと思ったのですが、どこか書き方がおかしいのでしょうか? ご存知の方、どうぞよろしくお願いいたします。

  • VBA DoEvents関数の働きと使い方を知りたい

    下記のような UserForm上の Module コードを書いてももらったのですが、DoEvents の働きが分からないのです。どなたか分かりやすく説明していただけませんでしょうか? Private i As Integer Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Me.TextBox1.Value = Me.Label1.Caption Then Me.Label2.Caption = "正解です" Else Me.Label2.Caption = "不正解です" End If DoEvents If i < 20 Then i = i + 1 Label_Up Me.TextBox1.Value = "" Cancel = True Else MsgBox "終了です" End If End Sub Private Sub UserForm_Initialize() i = 1 Label_Up End Sub Private Sub Label_Up() Me.Label1.Caption = Sheets("Sheet1").Range("A1:A20").Cells(i).Value DoEvents End Sub