Results 1 to 4 of 4

Thread: Error in Excel VBA code

  1. #1
    Join Date
    Jan 2009
    Posts
    84

    Error in Excel VBA code

    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.

  2. #2
    Join Date
    Dec 2008
    Posts
    161

    Error in Excel VBA code

    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

  3. #3
    Join Date
    Jan 2009
    Posts
    84

    Error in Excel VBA code

    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.

  4. #4
    Join Date
    Dec 2008
    Posts
    161

    Error in Excel VBA code

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

    You call the function passing 2 parameters:

    Code:
    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:

    Code:
    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:

    Code:
    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.

Similar Threads

  1. Replies: 6
    Last Post: 17-02-2012, 12:18 PM
  2. Error Code 4400:011A and Error Code 0F00:133C
    By mkwn in forum Operating Systems
    Replies: 1
    Last Post: 14-10-2011, 12:39 PM
  3. Replies: 9
    Last Post: 10-04-2009, 01:51 AM
  4. Windows Update Error Code 80070422 and Error code 1058
    By CloudJames in forum Windows Update
    Replies: 4
    Last Post: 10-01-2009, 10:44 AM
  5. Replies: 2
    Last Post: 28-06-2007, 12:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •