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

Question

前提・実現したいこと

現在、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を使用しています。

Comments

[ExcelVBAer]

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

[vba_begginer]

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

[ExcelVBAer]

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

[vba_begginer]

ありがとうございます。

Answer 1

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

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

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

Comments

[vba_begginer]

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

[ExcelVBAer]

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

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

[vba_begginer]

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

[TanakaHiroaki]

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

Answer 2

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

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
　　　’該当行（↓）を検索した後に、該当の行から検索語を１列ずつ検索し（→）、値を返す。     
      End If
    Next y
End With

Comments

[ExcelVBAer]

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

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

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

[vba_begginer]

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

[ExcelVBAer]

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

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

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

[vba_begginer]

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

Answer 3

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

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

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

Comments

[vba_begginer]

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