Printable View
Hello friends,
I have created a some code in VBA where the values are automatically searched in columns column C, I, K based on values selected by validation data columns column A and D, at the same time I have raised this issue on many forums but didn't got any positive response from them. Can anyone help me out with this.
Try to use the below code i hope this should help you some what
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
Thank you for replying me i have try to use the above code which you have provided me but when i am trying to run over here i am getting Runtime Error 91. i don't know why i am getting this message it might happen that i have copy pasted the code that's why i am getting this error message.
Sorry but i have forgetted to call function by passing 2 parameters
You call the function passing 2 parameters:
If you want tou can also put 3rd parameter as Optional:Code:Cells (Cel.Row, 3). Value = (Cells (Cel.Row, 1). Value, Cells (Cel.Row, 2). Value)
Cells (Cel.Row, 3). Value = S1 (Parameter1, Parameter2)
Or find the third parameter to include:Code:Private Function FOR (ByVal A As String, ByVal B As String, Optional ByVal c As String) As Variant
I have not tried to understand the syntax of the Excel formula, although I know many VBA, i am sure now you should not get any error.Code:Cells (Cel.Row, 3). Value = S1 (Parameter1, Parameter2, Parameter3)