• ベストアンサー
  • すぐに回答を!

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

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

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

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

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

2つ問題があると思います。 1.Application.WorksheetFunction.If WorksheetFunctionには「If」はありません。  単純にVBAのIFを使えば良いと思いますが、結果が「空白」か「0」かまたは  マッチしない時等対応を変えないとだめだと思います。 2.「worksheetFunctionクラスのVlookupプロパティを参照できません」  式は合っているとは思いますが、Range("Database3")が気になります。  試しにRange("A1:B9")等2列以上は問題がありませんが、Range("A1:A9")の1列の場合  上記エラーメッセージが表示されます。  「"Database3"」の範囲を確認した方が良いかと思います。

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

質問者からのお礼

こちらの件、あれから試行錯誤して動くようになりました。 おそらく、wbやwb2をきちんとactivateしてなかったのが、原因と思われます。 助言いただきましてありがとうございました。

質問者からの補足

お返事ありがとうございます。 さっそく試してみました。 1.そうだったのですね、if文は外しました。 On Error Resume Next~On Error GoTo 0で挟んでみたのですが問題ないでしょうか? 2.Set 範囲 = wb2.Worksheets("PvtSht2").Range("Database3")を MsgBox 範囲.Addressで試してみましたが、参照してほしい場所がきちんと選ばれていました。 範囲は2列です。 下記のように書き直してみたところ、 vlookupが1行ずつずれてしまうのです・・・ もしお分かりでしたら、ご指南いただけますか、よろしくお願い致します。 Sub test2() Dim wb As Workbook, wb2 As Workbook Dim r As Integer, intRow As Integer Dim 範囲 As Range Dim Database3 As Range Workbooks.Open Filename:="***.xlsm" Set wb = ThisWorkbook Set wb2 = ActiveWorkbook 'wb.Sheets("sheet1").Activate r = wb.Sheets("sheet1").Range("A28:N28").End(xlToRight).Column With wb2.Sheets("PvtSht2") .Range("A4:B" & .Range("A" & _ .Rows.Count).End(xlUp).Row).Name = "Database3" End With Set 範囲 = wb2.Worksheets("PvtSht2").Range("Database3") MsgBox 範囲.Address intRow = 3 With wb.Sheets("sheet1") Do Until .Cells(intRow, 1).Value = "" On Error Resume Next .Cells(intRow, (r + 1)) = Application.WorksheetFunction.VLookup(Cells(intRow, 1), 範囲, 2, False) intRow = intRow + 1 On Error GoTo 0 Loop End With wb.Activate End Sub

関連するQ&A

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

  • VBAの簡略化について

    VBAで数値をカウントするマクロを作りました。  Dim Co1 As Integer Co1 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<1") Dim Co2 As Integer Co2 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<2") Dim Co3 As Integer Co3 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<3") Dim Co4 As Integer Co4 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<4") Dim Co5 As Integer Co5 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<5") Dim Co6 As Integer Co6 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<6") Dim Co7 As Integer Co7 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<7") Dim Co8 As Integer Co8 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<8") Dim Co9 As Integer Co9 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<9") Dim Co10 As Integer Co10 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), "<10") Dim Co11 As Integer Co11 = WorksheetFunction.CountIf(Range(Cells(○, ○), Cells(○, ○)), ">=10") Cells(2, 1) = Co1 Cells(3, 1) = Co2 - Co1 Cells(4, 1) = Co3 - Co2 Cells(5, 1) = Co4 - Co3 Cells(6, 1) = Co5 - Co4 Cells(7, 1) = Co6 - Co5 Cells(8, 1) = Co7 - Co6 Cells(9, 1) = Co8 - Co7 Cells(10, 1) = Co9 - Co8 Cells(11, 1) = Co10 - Co9 Cells(12, 1) = Co11 Cells(2, 1) = "0&#65374;0.999" Cells(3, 1) = "1&#65374;1.999" Cells(4, 1) = "2&#65374;2.999" Cells(5, 1) = "3&#65374;3.999" Cells(6, 1) = "4&#65374;4.999" Cells(7, 1) = "5&#65374;5.999" Cells(8, 1) = "6&#65374;6.999" Cells(9, 1) = "7&#65374;7.999" Cells(10, 1) = "8&#65374;8.999" Cells(11, 1) = "9&#65374;9.999" Cells(12, 1) = "10&#65374;" これを短くする方法を教えてください。

  • 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

その他の回答 (1)

  • 回答No.2
  • myRange
  • ベストアンサー率71% (339/472)

  ちょっと見で気づいた点。 >VLookup(●Cells(intRow, 1), 範囲, 2, False)  ●の部分には .(ドット)が必要だと思いますが。。。   以上です。

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

質問者からのお礼

ありがとうございます。 こちらの件、直してみました。 ありがとうございました。

関連するQ&A

  • エクセル VBA もっときれいな書き方?

    Sub test() Dim i As Integer, n As Integer n = 1 For i = 2 To 150 If Cells(i, 1) <> Cells(i - 1, 1) Then Cells(i - 1, 5) = i - n Cells(i - 1, 6) = Application.WorksheetFunction.Sum(Range("B" & n & ":" & "B" & i - 1)) n = i End If Next i End Sub 上記のマクロですが Application.WorksheetFunction.Sum(Range("B" & n & ":" & "B" & i - 1)) この部分、もっとスマートに書く方法を教えてください。 Range("B" & n & ":" & "B" & i - 1)って、ちゃんと動きますが、書き方が何か変なような気がするんです。 よくわかってもいないのにすみません。

  • ExcelマクロでVLOOKを実行したい

    ExcelマクロでVLOOKを実行したい 同一シートにある「全体」の表から必要な項目をVLookで抜き出したくて 下記のマクロを作成しました。 「Sheet1」のA列(A2以下)には検索値(数字6ケタ)を入れています。 A2の検索値でヒットした値はB2・C2に入りましたが、A3以下の検索値は スルーされてしまいます。どこを直したら良いのか、ご教授ください。 よろしくお願いします。 ----------------------------------------------------------------------- Sub 検索して値を取得する() Dim 範囲 As Range Dim 検索値, i As Long Dim 出荷日 As Date Dim 商品名 As String Set 範囲 = Worksheets("全体").Range("E7:HG1000") Set 検索値 = Worksheets("Sheet1").Cells(i + 2, 1) If 検索値.Value <> "" Then 商品名 = Application.WorksheetFunction.VLookup(検索値, 範囲, 2, False) 出荷日 = Application.WorksheetFunction.VLookup(検索値, 範囲, 160, False) Cells(i + 2, 2).Value = 商品名 Cells(i + 2, 3).Value = 出荷日 i = i + 1 End If End Sub

  • 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を使う際に、別のシートの範囲を参照する方法

    はじめまして、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変数に変えたものです。 アドバイスお待ちしております。よろしくお願いします。

  • 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で別ブックの条件検索

    VBA初心者です。エクセルは2007です。 『データのあるブック(Book1,Book2,Book3)』と、『検索条件シート+出力先シートをもつブック』の4つのブックがあります。 検索条件シートで、L22でブック、P22でシートを指定してN22に入力した数に対応するデータをVlookupで出力先シートのセルに抽出されるようにしたいのですが、※の部分で「エラー438 オブジェクトは、このプロパティまたはメソッドをサポートしていません」とでて実行できません。 データのあるブックは同じ形式でシートには表があります。 数 a b c d 1 A B C D 2 ○ × △ ■ 3 Z Y X W     ・     ・ 検索条件がL22=3,P22=2,N22=2だとすると、Book3の2枚目のシートを検索し、 出力先シートのD1=○,J6=×,L23=△,J69=■となるようにしたいです。 本やインターネットで調べましたがわかりませんでした。 解決方法を教えていただきたいです。お願いします。 Sub 検索() Dim a, b, c, d As Range Dim 番号, ブック, シート As Integer With Workbooks("検索.xlsm").Sheets("検索条件") 数 = .Range("N22").Value ブック = .Range("L22").Value シート = .Range("P22").Value End With Dim wb As Workbook Dim sh As Worksheet Dim set範囲 As Variant With Workbooks("検索条件.xlsm").Sheets("出力先") Set a = .Range("D1") Set b = .Range("J6") Set c = .Range("L23") Set d = .Range("J69") End With Select Case ブック Case 1 Set wb = Workbooks("Book1.xlsm") wb.Activate Select Case シート Case 1 Set sh = Worksheets(1) Case 2 Set sh = Worksheets(2) Case Else MsgBox "・・・・・", vbExclamation, "nothing" End Select Case 2 Set wb = Workbooks("Book2.xlsm") wb.Activate Select Case シート Case 1 Set sh = Worksheets(1) Case 2 Set sh = Worksheets(2) Case Else MsgBox "・・・・・", vbExclamation, "nothing" End Select Case 3 Set wb = Workbooks("Book3.xlsm") wb.Activate Select Case シート Case 1 Set sh = Worksheets(1) Case 2 Set sh = Worksheets(2) Case Else MsgBox "・・・・・", vbExclamation, "nothing" End Select Case Else MsgBox "nothing", vbExclamation, "nothing" End Select ※Set set範囲 = wb.sh.Range("A4:E42")  ←エラー438 a = Application.WorksheetFunction.VLookup(数, set範囲, 2, False) b = Application.WorksheetFunction.VLookup(数, set範囲, 3, False) c = Application.WorksheetFunction.VLookup(数, set範囲, 4, False) d = Application.WorksheetFunction.VLookup(数, set範囲, 5, False) End Sub

  • エクセル VBA VLOOKUP

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

  • 日付セルを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 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)については、まったくわかりません。 マクロについて、自分でネットで勉強した程度なので、基本がわかっていないからなのでしょうが・・・。 どなたか、教えてください。 お願いします。

  • For~Nextの使い方

    こんにちは Range("B19").Value = Application.WorksheetFunction.Sum(Range("B4:B17")) Range("B55").Value = Application.WorksheetFunction.Sum(Range("B40:B53")) Range("B91").Value = Application.WorksheetFunction.Sum(Range("B76:B89")) Range("B127").Value = Application.WorksheetFunction.Sum(Range("B112:B125")) 上記を参考書等で見ながらやって見たのですが 動作しません。 For~Nextの使い方を教えて下さい。 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim x As Long, g As Long For x = 19 To 127 Step 36 For g = 4 To 112 Step 36 Cells(x, "B").Value = Application.WorksheetFunction.Sum(Cells(g, "B")) Exit For Next Next End Sub