Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Error in Excel VBA code

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 16-10-2009
Member
 
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.

Reply With Quote
  #2  
Old 16-10-2009
Member
 
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
Reply With Quote
  #3  
Old 16-10-2009
Member
 
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.
Reply With Quote
  #4  
Old 16-10-2009
Member
 
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Error in Excel VBA code"
Thread Thread Starter Forum Replies Last Post
Getting Error while opening Microsoft Excel 2000 file in Excel 2003 AbiaA MS Office Support 6 17-02-2012 01:18 PM
Error Code 4400:011A and Error Code 0F00:133C mkwn Operating Systems 1 14-10-2011 01:39 PM
WARNING: GetConfig failure, error = 0x80244021, soap client error = 10, soap error code = 0, HTTP status code = 502 LAMONT D Server Update Service 9 10-04-2009 02:51 AM
Windows Update Error Code 80070422 and Error code 1058 CloudJames Windows Update 4 10-01-2009 11:44 AM
WSUSand error 0x80244019, soap error code = 0, HTTP status code = Skamionek Windows Update 2 28-06-2007 01:10 AM


All times are GMT +5.5. The time now is 09:24 PM.