プロが教える店舗&オフィスのセキュリティ対策術

行列 A列 B列 C列 D列
 1  No  Kg  3  32
 2  1  30
 3  1  31
 4  1  32
 5  1  33
 6  1  34
 7  2  27
 8  2  28
 9  2  29
10  2  30
11  3  31
12  3  32
13  3  33



上表の通り、A列とB列がデータ(1行目はヘッダー)、セル番号C1とD1が検索条件です。
C1の検索条件はA列、D1の検索条件がB列です。

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

以下のように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
gooドクター

A 回答 (4件)

こんにちは。



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

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

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にする
    • good
    • 0
この回答へのお礼

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

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

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

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


》 演算式で、*ROW(A1:A15)) を掛け算するというのが
》 理解できてないのですが・・・
お断りしておきますが、マクロ音痴の私は提示した演算式の意味についてしかコメントできません。
貴方の理解の一助になろうかと思ったのが添付図だけど、マクロが使える貴方なら「なるほど」と参考になるのでは?
「複数の条件に合う行番号を取得するには」の回答画像3
    • good
    • 0

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


=SUMPRODUCT((A1:A15=C1)*(B1:B15=D1)*ROW(A1:A15))
この回答への補足あり
    • good
    • 3

こんにちは!



一例です。
いかにも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
    • good
    • 2

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング