Error in Excel VBA code

[Unnat]

Sponsored Links

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.

[Alejandro]

Try to use the below code i hope this should help you some what

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

[Unnat]

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.

[Alejandro]

Sorry but i have forgetted to call function by passing 2 parameters

You call the function passing 2 parameters:

Cells (Cel.Row, 3). Value =  (Cells (Cel.Row, 1). Value, Cells (Cel.Row, 2). Value) 
Cells (Cel.Row, 3). Value = S1 (Parameter1, Parameter2)

If you want tou can also put 3rd parameter as Optional:

Private Function FOR (ByVal A As String, ByVal B As String, Optional ByVal c As String) As Variant

Or find the third parameter to include:

Cells (Cel.Row, 3). Value = S1 (Parameter1, Parameter2, Parameter3)

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.