ExcelVBA 条件に一致したセルのある行を検索

ExcelVBA

Match関数がいいらしい。
でも同じ条件の行があったら最初の行しか返してくれない

見つからないと例外にあんるので on error resume next も欠かせない。

Sub match()
    Dim workSh, prefSh As Worksheet
    Set workSh = ThisWorkbook.Worksheets("Sheet1")
    Set prefSh = ThisWorkbook.Worksheets("Sheet2")

    Dim prefRng As Range
    Set prefRng = Range(prefSh.Cells(2, 2), prefSh.Cells(48, 2))

    Dim workEndR, workTmpR As Long, tmpStr As String
    workEndR = workSh.Cells(Rows.Count, 1).End(xlUp).Row

    For workTmpR = 2 To workEndR
        tmpStr = workSh.Cells(workTmpR, 1).Value
        On Error Resume Next
        workSh.Cells(workTmpR, 2).Value = prefSh.Cells(Application.WorksheetFunction.match(tmpStr, prefRng, 0) + 1, 1)
        If Err <> 0 Then
            workSh.Cells(workTmpR, 2).Value = "ERROR"
            Err.Clear
        End If
    Next
End Sub

ネタ元