質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.49%

WorksheetFunctionでINDEXとMATCHを使って、ある文字列を別シートで検索後、同じ行、別の列の値を返したい

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 23K+

score 6

 前提・実現したいこと

現在、VBAであるデータベースから検索した値を表示させる検索ツールを作っています。
WorksheetFunctionでINDEXとMATCHを使って、ある文字列を別シートで検索後、別の列の指定のセル値(文字列)を返すようなコードを作っています。
散々試行錯誤した結果、以下のエラーメッセージが出てしまいました。

 発生している問題・エラーメッセージ

ある条件検索に基づいた、指定列の文字列が返せません。

WorksheetFunctionクラスのINDEXプロパティを取得できません

 該当のソースコード

Sub 検索テスト()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

    Dim 検索ブック, データベース As Workbook
    Dim 検索シート, データシート As Worksheet

    Workbooks.Open _
    "データベース.xlsx" _
    , UpdateLinks:=0

    Set 検索ブック = Workbooks("検索システム.xlsm")
    Set データベース = Workbooks("データベース.xlsx")
    Set 検索シート = 検索ブック.Worksheets("検索シート")
    Set データシート = データベース.Worksheets("データシート")

    Dim i, j As Long
    Dim 検索値 As Range
    Set 検索値 = Range(検索シート.Cells(8, 7), 検索シート.Cells(7000, 7))

  For i = 8 To 7000
        For j = 2 To 5670
        On Error Resume Next
            If 検索シート.Cells(i, 7).Value = データシート.Cells(j, 2).Value _
                And 検索シート.Cells(i, 8) = データシート.Cells(j, 26) Then
                検索シート.Cells(i, 9).Value = Application.WorksheetFunction. _
                Index(データシート.Cells(j, 27), Application.WorksheetFunction. _
                Match(検索シート.Cells(i, 7), Range(データシート.Cells(2, 2), データシート.Cells(5670, 2)), 0))
            End If
        Next j
    Next i

    データベース.Close False
    検索ブック.Worksheets(1).Activate

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

 試したこと

変数をi,jに分けて、ループ処理のパターンを2種類にしてみました。

 補足情報(FW/ツールのバージョンなど)

Excel 2010を使用しています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ExcelVBAer

    2018/04/10 16:07

    VBAを「</>」ボタンでセットされる[```]の間に記載できませんか?

    キャンセル

  • vba_begginer

    2018/04/10 16:26

    出来ました。見にくくなっていてすみませんでした。

    キャンセル

  • ExcelVBAer

    2018/04/10 16:27

    すごく見やすくなりました。

    キャンセル

  • vba_begginer

    2018/04/10 16:28

    ありがとうございます。

    キャンセル

回答 3

+1

下記の「Range」にシートが付いてないのが原因の可能性があります。
「データシート.Range(」と記載した方が無難です。

Application.WorksheetFunction.Index(データシート.Cells(j, 27), Application.WorksheetFunction.Match(検索シート.Cells(i, 7), Range(データシート.Cells(2, 2), データシート.Cells(5670, 2)), 0))

こういう風に何回もシートオブジェクトが出てくると見にくいので、
With データシート 等として、「データシート」を省略する等すると見やすくなります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/04/11 09:28

    ご教示誠に有り難うございます。
    無事エラーメッセージはでなくなり、且つ、withステートメントを使ったことでコードが見やすくなりました。
    ただ、動きがものすごく遅いです。findメソッドやvlookupを使わず高速化する目的で上記コードを作りましたが、浅はかだったようです。。。高速化する案があればご教示いただけますと幸いです。

    キャンセル

  • 2018/04/11 12:00

    Screenupdate や Calculation とか、基本的な事はご自分で調べてくださいね。

    自分なら、シート上のデータを配列に格納して検索しますかねぇ。
    ただ、配列に格納すると、配列の要素数が1から始まるので、
    ご自分で都合いいように調整してください。

    キャンセル

  • 2018/04/11 13:11

    ScreenupdateやCalculationあたりは大丈夫です。
    配列への格納は、確かに使えそうです。一度やってみます。
    初心者にも分かりやすいご回答、誠に有り難うございます!

    キャンセル

  • 2018/04/19 16:39

    シート上のデータに重複がない場合、当該列をインデックス、別の列の値(文字列)をキーとする連想配列を用いれば、高速に処理できる可能性があります。

    キャンセル

check解決した方法

0

以下のように修正しました。

With データシート
    Dim y As Long, 該当行 As Long, 該当列 As Long
    Dim 検索値 As String
    For y = 8 To 7000
     該当行 = Application.WorksheetFunction.Match(検索シート.Cells(y, 7), .Range(.Cells(2, 2), .Cells(5670, 2)), 0)

      If 該当行 <> 0 Then
      On Error Resume Next
        該当列 = .Range(.Cells(該当行 + 1, 26), .Cells(該当行 + 1, 71)).Find(what:=検索シート.Cells(y, 8), lookat:=xlWhole).Column
   ’該当行(↓)を検索した後に、該当の行から検索語を1列ずつ検索し(→)、値を返す。     
      End If
    Next y
End With

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/04/17 09:54

    よくできました。
    処理はちゃんと個別に分けるべきです。
    じゃないと、ちょっとした小石でこけてしまいますから。

    で、コードを載せる際は、『</>』ボタンを使ってください。
    インデントが無いコードは見るに堪えませんので。

    あと、該当列 = .Range().Find.().Column としてますが、
    これ、Findが失敗した時、該当列 の中身はどうなるでしょう?
    該当列 に 0意外の値が入った状態で確認しておいた方がいいと思いますよ。

    キャンセル

  • 2018/04/17 13:03

    ご教示、誠に有り難うございます。
    『</>』ボタンの使い方をご教示いただけますと幸いです。基本的なことですみません。
    On Error Resume Nextを使っても該当列にエラーが生じる可能性があるということでしょうか。
    一度確認してみます。

    キャンセル

  • 2018/04/17 14:43 編集

    On Error Resume Next ている際に、
    エラーが生じていた場合にはもちろんデバッグにはなりません。

    しかし、その場合の挙動( 該当列 の値がどうなるか)について、
    確認できていないのであれば、知っておかないと致命的なエラーになるでしょうね。

    『</>』ボタンは、回答する際に該当ボタンを押せば分かりますよ。
    ```ここに言語を入力
    コード
    ```
    ※回答に対するコメントでは使えません。

    キャンセル

  • 2018/04/17 15:27

    できました!有り難うございました。
    エラーの可能性があるとのことですので、確認しておきます。
    いろいろとご教示、誠に有り難うございます。

    キャンセル

0

以下のように変えてみて欲しい。

On Error Resume Next 
検索シート.Cells(i, 9).Value = Application.WorksheetFunction.Index(データシート.Cells(j, 27)....
On Error GoTo 0

高速化もできるかと思うけど?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/04/17 09:21

    ご教示誠に有り難うございます。On Error Resume Nextを入れてみました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • VBAに関する質問
  • WorksheetFunctionでINDEXとMATCHを使って、ある文字列を別シートで検索後、同じ行、別の列の値を返したい