Sub distribute()
Dim rge, vt1, vt2, roomNo, buildNo
Dim i%, j%, k%, n%
Set rge = Range(Range("D2:K2"), Range("D2:K2").End(xlDown))
vt1 = rge
vt2 = Range(Range("M3:P3"), Range("M3:P3").End(xlDown))
For i = 1 To UBound(vt1, 1)
rge(i, 8).ClearContents
roomNo = Right(vt1(i, 3), 1)
For j = 1 To UBound(vt2, 1)
If vt1(i, 2) = vt2(j, 2) Then
If vt2(j, 3) <> "" Then
If vt1(i, 1) = vt2(j, 1) And vt2(j, 3) Like "*" & roomNo & "*" Then
rge(i, 8) = vt2(j, 4)
Exit For
End If
ElseIf vt1(i, 1) = vt2(j, 1) Then
rge(i, 8) = vt2(j, 4)
Exit For
End If
Else
If vt1(i, 1) = vt2(j, 1) And vt2(j, 2) Like "*" & vt1(i, 2) & "*" Then
rge(i, 8) = vt2(j, 4)
Exit For
End If
End If
Next
Next
End Sub |