Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range, s As String, S1 As Range, R1 As Range, P1 As Range
If Not Intersect(Target, Union([A15:A19], [D15:D19])) Is Nothing Then
For Each Cel In Intersect(Target, Union([A15:A19], [D15:D19])).Cells
Application.EnableEvents = False
Cells(Cel.Row, 3).Value = S1(Cells(Cel.Row, 1).Value, Cells(Cel.Row, 2).Value)
Cells(Cel.Row, 9).Value = R1(Cells(Cel.Row, 1).Value, Cells(Cel.Row, 2).Value)
Cells(Cel.Row, 11).Value = P1(Cells(Cel.Row, 1).Value, Cells(Cel.Row, 2).Value)
Application.EnableEvents = True
Next Cel
End If
End Sub
'
Private Function P1(ByVal A As String, ByVal B As String, ByVal C As String, ByVal D As String) As Variant
P1 = Evaluate("=OFFSET(INS1X(Cos1!$A$1:$P$11,MATCH(""" & _
D & """,OFFSET(Cos1!$B$1:$B$11,0,MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)-2),0),MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)),0,2)")
If IsError(P1) Then P1 = ""
End Function
Private Function R1(ByVal A As String, ByVal B As String, ByVal C As String) As Variant
R1 = Evaluate("=OFFSET(INS1X(Cos1!$A$1:$P$11,MATCH(""" & _
C & """,OFFSET(Cos1!$A$1:$A$11,0,MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)-2),0),MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)),0,1)")
If IsError(R1) Then R1 = ""
End Function
Private Function S1(ByVal A As String, ByVal B As String, ByVal C As String) As Variant
S1 = Evaluate("=OFFSET(INS1X(Cos1!$A$1:$P$11,MATCH(""" & _
C & """,OFFSET(Cos1!$A$1:$A$11,0,MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)-1),0),MATCH(""" & _
A & """,Cos1!$A$1:$P$1,0)),0,-1)")
If IsError(S1) Then S1 = ""
End Function
Bookmarks