Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
MatchでAND条件にすると
投稿日時: 22/03/03 18:21:33
投稿者: HKD20220303

久しぶりにこちらのサイトに投稿します。
お分かりの方がいらっしゃいましたら、どうぞよろしくお願いいたします。
 
Match関数で複数の条件を指定しようとしています。
具体的には、住所録の中から、名前と住所が部分一致した行を取り出したい。
(住所録は70万件くらい)
skaに名前を、skbに住所をそれぞれ試したところ、単独の条件ではうまく行きました。
MValR = WorksheetFunction.Match("*" & ska & "*", Range(Cells(1, 5), Cells(50, 5)), 0)
MValR = WorksheetFunction.Match("*" & skb & "*", Range(Cells(1, 12), Cells(50, 12)), 0)
上記はそれぞれうまく行きました。
 
これらの条件をAndでつないだら、
MValR = WorksheetFunction.Match("*" & ska & "*" And "*" & skb & "*", Range(Cells(1, 5), Cells(50, 5)) And Range(Cells(1, 12), Cells(50, 12)), 0)
実行時エラー'13'
型が一致しません。
のエラーになってしまいます。
 
そもそもMatch関数はこのような使い方はできないものでしょうか。
ifやfindは試していませんが、処理が遅いと情報があったので、Match関数で複数行を取り出そうと思っています。

回答
投稿日時: 22/03/03 20:36:13
投稿者: WinArrow
投稿者のウェブサイトに移動

>そもそもMatch関数はこのような使い方はできないものでしょうか。
MATCHでは対応できないでしょう。
 
御承知と思いますが、
Match関数で取得できるのは、最初の一件目だけです。
 
代案として
ADOを使った方法を提案します。
↓参考にしてください。
https://qiita.com/t-yama-3/items/e79056079ffdcdb79391

回答
投稿日時: 22/03/03 22:39:03
投稿者: simple

MATCHは、縦一列もしくは横1行を対象に検索するものです。
そのような検索はできません。(文字列連結を予めしておく手はあるでしょう)
しかし、既に回答がありますように、MATCHは最初にヒットしたものしか返さないところが致命的です。
 
>ifやfindは試していませんが、処理が遅いと情報があった
何と比べているのか知りませんが、
ソート済みの項目を対象に"2分探索"アルゴリズムを利用する
MATCHやVLOOKUPの近似一致検索と比較すれば、そちらが早いですが、
特別な条件の下でしか言えません。
https://archives.aotsuki.org/excel-functions-optimize-lookups/
 
さて、マッチしたすべてを取り出すのであれば、

(1)FINDで繰り返し検索し、ヒットした都度、他方の条件を満たしているか確認する方法。
   (FINDのヘルプに繰り返し検索の例文があるので参考になるでしょう。)
(2)フィルタオプションの利用
(3)オートフィルタの利用
(4)その他  
が考えられるでしょう。
いずれもネットを検索すれば、コード例などがあります。調べてみてください。

回答
投稿日時: 22/03/03 23:04:34
投稿者: 半平太

>MValR = WorksheetFunction.Match("*" & ska & "*", Range(Cells(1, 5), Cells(50, 5)), 0)
>MValR = WorksheetFunction.Match("*" & skb & "*", Range(Cells(1, 12), Cells(50, 12)), 0)
>上記はそれぞれうまく行きました。
 
別々にうまく行っても、And条件で成立しているとは限らないです。
skaで合致した行にskbもあるかどうかチェックする必要があります。
 
Matchを使っても70万行もあると、どれだけ速度が出るか疑問ではありますが、、
 

Sub sample()
    Dim lastRow As Long
    Dim ska, skb
    Dim MValR
    
    Dim CNT As Long
    Dim RW As Long
    
    ska = "ska"
    skb = "skb"
    lastRow = 50
    
    For RW = 1 To lastRow
        MValR = Application.Match("*" & ska & "*", Range(Cells(RW, 5), Cells(lastRow, 5)), 0)
        
        If IsNumeric(MValR) Then
            RW = RW - 1 + MValR
            CNT = Application.CountIf(Cells(RW, 12), "*" & skb & "*")
            
            If CNT Then        
                Debug.Print RW  '該当行書き出し
            End If
        Else
            Exit Sub
        End If
    Next RW
End Sub

投稿日時: 22/03/04 09:42:03
投稿者: HKD20220303

WinArrow様
simple様
半平太様
早々にご回答いただき、ありがとうございます。とても参考になりました。
やはりMATCH関数ではできないのですね。FINDを活用する方法を検討してみます。