複数の条件に合う行番号を取得するには

行列　Ａ列　Ｂ列　Ｃ列　Ｄ列
　１　　No　 Kg　　3　　32
　２　　1　　30
　３　　1　　31
　４　　1　　32
　５　　1　　33
　６　　1　　34
　７　　2　　27
　８　　2　　28
　９　　2　　29
１０　　2　　30
１１　　3　　31
１２　　3　　32
１３　　3　　33



上表の通り、Ａ列とＢ列がデータ(１行目はヘッダー）、セル番号Ｃ１とＤ１が検索条件です。
Ｃ１の検索条件はＡ列、Ｄ１の検索条件がＢ列です。

この２つの条件に合う行番号を取得できるようにVBAを作成しております。
上記例ですと、行番号（アウトプット）は１２行目となります。

以下のようにMatch関数では複数条件での検索ができなそうです。
（以下は当然エラーとなってしまいます）

調べていると、IndexやFind 関数を使用している例も見ましたが、うまくいきません。

複数の条件から該当する行番号を返す方法をご教授いただきたくお願いします。




Sub test()

Dim row As Variant

row = Application.Match(Range("C1") & Range("D1"), Range("A2:A13") & Range("B2:B13"), 0)

If IsError(row) Then

MsgBox "該当データが見つかりません"
Else

MsgBox row & "番めのデータです"

End If

End Sub

質問者からの補足コメント

• 

  回答ありがとうございます。
  
  Dim ret As Double
  
  ret = WorksheetFunction.SumProduct((Range("A1:A15") = Range("C1")) * (Range("B1:B15") = Range("D1")) * Row(Range("A1:A15")))
  
  
  
  ROWのところで、「Sub又はFunctionが定義されていない」　とエラーが出ます。
  この演算式で、*ROW(A1:A15))　を掛け算するというのが理解できてないのですが・・・

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/04/17 09:50

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2016/04/17 13:40

こんにちは。

Match関数は、VBAではよく使いますが、主に、１次元配列で使われるものですから、今回のようなものには不向きです。また、VBAでは、配列関数はほとんど使われません。なぜかというと不要だからです。Macro3 をみてください。

この３つのマクロを比べてみてください。
ただし、該当するデータは複数入れてみるとよいです。

3　32　　複数ある場合。
3　32
33　2　合わせると同じ文字になってしまう場合

本来は、これらを想定して作らなくてはならないはずです。

最後に、ユーザー定義関数(UDF)を加えて置きましたが、これは、配列の出力がありますから、複数の場合は、INDEX関数などが必要です。下手なマクロかもしれませんが、少しでも参考になればと思いました。

'---------------------
Sub Macro1()
Dim c As Range
Dim a As Variant
Dim b As Variant
Dim adr As String
a = Range("C1").Value
b = Range("D1").Value
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
　　If c.Value = a Then
　　　　If c.Offset(, 1).Value = b Then
　　　　　　adr = adr & "," & c.Address(0, 0)
　　　　 End If
　　 End If
Next c
　If Len(adr) > 1 Then
　　　　MsgBox Mid(adr, 2)
　Else
　　　　MsgBox "該当のセルは見つかりません", vbExclamation
　End If
End Sub
'------------------
Sub Macro2()
　　Dim rw As Variant
　　'=MATCH(検査値,検査範囲,照合の種類)
　　Dim a As Variant
　　Dim b As Variant
　　Dim i As Long
　　Dim ar1 As Variant
　　Dim ar2 As Variant
　　ar1 = Application.Transpose(Range("A2:A15").Value)
　　ar2 = Application.Transpose(Range("B2:B15").Value)
　　a = Range("C1").Value
　　b = Range("D1").Value
　　For i = LBound(ar1) To UBound(ar1)
　　　　ar1(i) = ar1(i) & "," & ar2(i)
　　Next i
　　rw = Application.Match(a & "," & b, ar1, 0)
　　If IsNumeric(rw) Then
　　　　MsgBox rw & "番めのデータです"
　　Else
　　　　MsgBox "該当データが見つかりません", vbExclamation
　　End If
End Sub
'---------------
Sub Macro3()
Dim i As Variant
　　 i = Evaluate("SUM((A1:A15=C1)*(B1:B15=D1)*ROW(A1:A15))")
　　 If i <> 0 Then
　　　　　　MsgBox i
　　 Else
　　　　　　MsgBox "該当のデータが見つかりません", vbExclamation
　　 End If
End Sub
'------------------
Function SearchMatch(ser1, ser2, rng1 As Range, rng2 As Range)
'これは配列関数です。
　　Dim RwCnt As Long: RwCnt = rng1.Rows.Count
　　Dim i As Long, j As Long
　　Dim Ar As Variant
　　Dim Ret() As Variant
　　ser1 = Trim(ser1)
　　ser2 = Trim(ser2)
　　ReDim Ar(1 To RwCnt)
　　For i = 1 To RwCnt
　　　　Ar(i) = rng1.Cells(i, 1).Value & "," & rng2.Cells(i, 1).Value
　　　　If ser1 & "," & ser2 Like Ar(i) Then
　　　　　　 j = j + 1
　　　　　　 ReDim Preserve Ret(1 To j)
　　　　　　 Ret(j) = i
　　　　End If
　　Next i
　　If j > 0 Then
　　　　SearchMatch = Ret
　　Else
　　　　SearchMatch = "n/v"
　　End If
End Function

'//
配列の出力は、
例えば、このように出汁ます。
=INDEX(SearchMatch(C1,D1,A2:A14,B2:B14),,1)
=INDEX(SearchMatch(C1,D1,A2:A14,B2:B14),,2) '2にする

この回答へのお礼

Macro３が一番スマートで期待した通り動作しました。
ありがとうございます。

今後の参考にもさせていただきます。

お礼日時：2016/04/17 22:54

No.3 回答者： mike_g 回答日時： 2016/04/17 10:35

[No.2補足]へのコメント、

》 演算式で、*ROW(A1:A15))　を掛け算するというのが
》 理解できてないのですが・・・
お断りしておきますが、マクロ音痴の私は提示した演算式の意味についてしかコメントできません。
貴方の理解の一助になろうかと思ったのが添付図だけど、マクロが使える貴方なら「なるほど」と参考になるのでは？

No.2 回答者： mike_g 回答日時： 2016/04/16 20:17

行番号は次式で得られるので、「VBAを作成」するのはご自分でネ。

=SUMPRODUCT((A1:A15=C1)*(B1:B15=D1)*ROW(A1:A15))

No.1 回答者： tom04 回答日時： 2016/04/16 11:05

こんにちは！

一例です。
いかにもVBAって感じでやってみました。
ちょっとまわりくどいかもしれませんが・・・

Sub Sample1()
Dim c As Range, myRng As Range, myFlg As Boolean
Dim myFound As Range, myFirst As Range
Set myFound = Range("A:A").Find(what:=Range("C1"), LookIn:=xlValues, lookat:=xlWhole)
If Not myFound Is Nothing Then
Set myFirst = myFound
Set myRng = myFound
Do
Set myFound = Range("A:A").FindNext(after:=myFound)
If myFound.Address = myFirst.Address Then Exit Do
Set myRng = Union(myRng, myFound)
Loop
For Each c In myRng
If c.Offset(, 1) = Range("D1") Then
myFlg = True
Exit For
End If
Next c
If myFlg = True Then
MsgBox c.Row & "番目のデータです"
Exit Sub
Else
GoTo EH
End If
Else
GoTo EH
End If
EH:
MsgBox "該当データなし"
End Sub

※　単に一致した行番号をメッセーボックスに表示させているだけですので、
「○番目」というコトであれば
＞MsgBox c.Row -1 & "番目のデータです"
としないといかないかも・・・

こんな感じではどうでしょうか？m(_ _)m