ワークシート関数（INDEX関数とMATCH関数）を使用して、任意の値を抽出したい

Question

前提・実現したいこと

現在、VBAでマスタデータから検索した値を抽出させるコードを作成しております。
INDEXとMATCHを使用したワークシート関数を、WorksheetFunctionで同じように入力をしていますがうまく作動しません。

下記のような関数を使用しています。
=index(マスタ!C:C,match("JPY"&"取引先名",index(マスタ!A:A&マスタ!B:B,0),0))
INDEX関数を用いてマスタシートのA列の通貨、B列の取引先名から一致しているものを検索し、C列のコードを抽出する式です。

関数の式に関して、同取引先に対しても案件毎に行追加して管理しており、重複が発生するためsumproduct関数は使用できませんでした。

普段は関数を使用して更新しているのですが、計算式が多くファイルが重くなり始めたのでVBAを使用してファイルの軽量化を図り今回ご質問させていただきます。

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

実行時エラー'13':
型が一致しません。

該当のソースコード

Sub マスタ抽出()
With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
End With

Dim master As worksheet, tenki as worksheet
set master = ThisWorkbook.Worksheets("マスタ")
set tenki = ThisWorkbook.Worksheets("転記用シート")

Dim end
end = tenki.Cells(Rows.Count, 1).end(Xlup).Row

Dim i As Long
 For i = 1 To end
  tenki.Cells(i, 2) =worksheetfunction.Index(master.columns("C"),worksheetfunction.Match("JPY"&tenki.Cells(i,1),worksheetfunction.Index(master.columns("A")& master.columns("B") ,0),0))
 Next

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

試したこと

マスタのC列のコードには数値だけでなく文字列も含まれています('0000のように桁数を固定しているため頭に0がくるものがある)。iの変数をVariantにしてみても型が一致しませんでした。

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

Excel 2013を使用しています。

Answer 1

Option Explicit

Sub test()
    Dim rngList As Range
    Dim rngData As Range
    Dim strListAddress As String
    Dim strFindAddress As String
    Dim strFormula As String
    
    Set rngList = Worksheets("マスタ").Range("A1").CurrentRegion
    Set rngData = Worksheets("転記用シート").Range("A1").CurrentRegion
    
    strListAddress = rngList.Address(True, True, xlA1, True)
    With rngList.Columns("D")
        .Formula = "=A1&B1"
        strFindAddress = .Address(True, True, xlA1, True)
    End With
    strFormula = "=Index(" & strListAddress & ",Match(D1," & strFindAddress & ",0),3)"
    
    With rngData
        .Columns("D").Formula = "=A1&B1"
        With .Columns("C")
            .Formula = strFormula
            .Value = .Value
        End With
        .Columns("D").ClearContents
    End With
End Sub

こういうことですか？
なんで、数式を纏めようとするんだろ。。。
段階的に手順を踏むとわかりやすいと思うのですが。。。

そもそも、再計算を手動にしておいて、
追加分の範囲にフィルハンドルをダブルクリックで数式をコピーし、
F9キー押下で再計算したらとか思うのですが。。。

あーそれと。ループしながら、各セルの計算を個々にしてたら、
まぁまぁ、遅いと思います。

Comments

[beginner_2020]

ご教示いただきありがとうございます。
関数しか触ったことが無く、こういったいかにもVBAなものをかける方は尊敬します。
理解が及んでいないのですが、参考にさせて頂き、頂いたコードで挑戦して見たいと思います。

Answer 2

データがどのようなものなのかが分からないので、、、。

デバッグした際に、きちんと期待する値になってましたでしょうか？
１つの処理の中に複数処理が入り組んでいるので、
イミディエイトやウォッチなどで１つ１つ細かく見るとよいかと思います。

VBA デバッグの仕方

Comments

[beginner_2020]

ご教示いただきありがとうございます。
デバッグで１つ１つみていきたいと思います。

Answer 3

Indexで2列を対象に書けることを知りませんでした。勉強になりました。
ですがVBAではその書き方ができないようですね。
面倒ですが、2回に分けて結果を結合するのがよいかと思います。
また一文で書くと非常にわかりづらいので、各関数を分割した方がよいかと思います。

a1 = WorksheetFunction.Index(master.Columns("A"), 0)
a2 = WorksheetFunction.Index(master.Columns("B"), 0)
a = a1 & a2
b = WorksheetFunction.Match("JPY" & tenki.Cells(i, 1), a, 0)
c = WorksheetFunction.Index(master.Columns("C"), c)
tenki.Cells(i, 2) = c

ちなみにですが、エラーになっていたworksheetfunction.Index(master.columns("A")& master.columns("B") ,0)の箇所ですが、これって結局のところA1セルとB1セルを取得しているだけですよね？
であるならIndexなど使わず直接参照してもよいかと思います。

tenki.Cells(i, 2) =worksheetfunction.Index(master.columns("C"),worksheetfunction.Match("JPY"&tenki.Cells(i,1),master.Cells(1,1)&master.Cells(1,2),0))

また余談ですがendはそういう命令があるので使えません。
最初の方でエラーになるはずですが。。。

更に余談ですが、Index関数に指定する行番号は1オリジンです。
0を指定しても1とみなされるようですが、そこらへんも一応見直してみてください。

Comments

[beginner_2020]

ご教示いただきありがとうございます。
分割するとだいぶ見やすくなりますね！
ただ、今度はｂのところで「実行時エラー'1004':WorksheetFunctionクラスのMatchプロパティを取得できません」のエラーが発生してしまいました。検索値に文字列と数値が混在するケースがあるのが原因でしょうか。

worksheetfunction.Index(master.columns("A")& master.columns("B") ,0)
これはMATCH関数の中の検索範囲に該当します。AとBの列全体を参照して、検索値と合致する行番号をINDEX関数に渡したいところです。

すいません、endのところはendmaxの転記ミスです汗
Index関数の行番号についてもありがとうございます、修正してみました。

[ttyp03]

こちらで動かした限りでは「Matchプロパティを取得できません」というエラーは発生していません。
たいして動作確認もせず回答しましたが、Indexの第二引数を0にしていると、Indexで返されるのは配列のようですね。
なのでやはり0ではなく1を指定しないといけなさそうです。
そこが影響しているのかはわかりませんが。

ちなみにIndexで2列を対象とすると、回答に書いたように2列が結合した値が返されます。
|A|1|
|B|2|
上記のような表に対して、
index(マスタ!A:A&マスタ!B:B,1)
を実行すると、「A1」というものが返されますが、これは想定通りでしょうか？

[beginner_2020]

Indexは1を指定してMatchプロパティのエラーがでてしまいました。
返されるものはおそらく想定通りだと思います。
↓イメージはこのような感じです。A列に管理通貨、B列に取引先名、C列にコードです。
A列 |B列|　C列
|JPY |A社| 0001|
|USD|A社| 0002|
|JPY |B社| 0003|
円取引のものを抽出するときに、A列がJPYで、なおかつ社名がA社のものを検索し、合致する行のC列のコードをもってくる処理です。もしかして返されるものが違うのでしょうか。

[ttyp03]

この仕様だとたぶん期待する結果にはならないと思います。
これならSUMIFSあたり使えば一発だと思います。
=SUMIFS(C:C,A:A,"JPY",B:B,"A社")
SUMIFSはWorksheetFunctionにも存在するので使ってみてください。
こんな感じでしょうか。
tenki.Cells(i, 2) = WorksheetFunction.SumIfs(master.Columns("C"), master.Columns("A"), "JPY", master.Columns("B"), tenki.Cells(i, 1))
要件通りかわからないので試してみてください。