|
Option Explicit
'----------------------------------------------------------------
Function get_subject(rngX As Range, rngY As Range) As Variant
'----------------------------------------------------------------
'Dim rngX As Range: Set rngX = [B2:E14]
'Dim rngY As Range: Set rngY = [M8:P8]
Dim vY As String: vY = _
_
Join( _
WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(rngY.Value)), _
",")
Dim row As Range
Dim sSubject As String
For Each row In rngX.Rows
If Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(row.Value)), ",") = vY Then
If sSubject = "" Then
sSubject = row.Cells(1).Offset(0, 6).Value
Else
sSubject = sSubject & "/" & row.Cells(1).Offset(0, 6).Value
End If
End If
Next row
get_subject = sSubject
End Function
|
|