Run Time Error 13 Type Mismatch [SOLVED]

[gauravkrgautam]

Hi all my seniors,


After a long day i have created a macrobased file with these specifications.

1. get data in submission form from data file (just fetch data from 2)

2. update data from submission form to data file (keeps records of latest submission)

3. maintaing records in overall sheets for submission records (keep records of all submission)

all are working f9 but gives error on deleting any of the value (cells with value without background colors) on submission form


what could be the reason i am unable to come out of this.

module 2 has been removed from sample file which was for updating values in sheet 2 & 3.

pls help me out


http://rapidshare.com/files/2626709741/Input%20tester.xlsm


thanks in advance

 

[NARAYANK991]

Hi Gaurav ,


If you want others to troubleshoot the macros , please either post the password , or upload the file after removing the protection.


Narayan

 

[gauravkrgautam]

oopss i forgot to unprotect

Thanks Narayank991 for replying to my post. and sorry for uploading protecting file here is the new one.


http://rapidshare.com/files/447449128/1%20Input%20tester.xlsm


thanks in advance

 

[NARAYANK991]

Hi Gaurav ,


Replace the current Worksheet_Change event procedure in the sheet tab labelled Submission Form by :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$E$3")) Is Nothing Then Exit Sub
If Target = vbNullString Then Exit Sub
Call Test
End Sub

[/pre]
Of course , in your Test procedure , you should be checking to see whether the customer exists before doing a VLOOKUP ; otherwise , disable error trapping before the VLOOKUP and enable it after , so that you don't get any runtime errors. Ideally , you should have an error handler which does all of the above , and also displays a message for the user.


Narayan

 

[gauravkrgautam]

Thanks NAYRAYANK991 for RESOLVING this problem

after changing my coding with yourones my work is going perfectly.

but perhaps i am unable to understand this coding.

as per me (i am beginner in vba) application.intersect gives out a common range, in above code we have only one range("$e$3") and line3 also confusing me. pls explain me this and pardon me if i am out of topic anywhere.

 

[NARAYANK991]

Hi Gaurav ,


No problem.


Application.Intersect is a function which returns the intersection of two or more ranges which may be passed as parameters to it ; thus :


Application.Intersect(a,b) will return :


a) Nothing ( which is a VBA keyword ) , and can be used for testing ; the test will return TRUE or FALSE


b) the point(s) of intersection as a range


For instance , in the Immediate Window , enter the following :

[pre]

?Application.Intersect(Range("A:A"),Range("3:3")).Address
The answer will be : [b]$A$3


Now enter the following :

[pre][code]?Application.Intersect(Range("A15:C25"),Range("B13:D23")).Address
The answer will be : [b]$B$15:$C$23


However , to check for [b]Nothing , we cannot do the following :

[pre][code]?Application.Intersect(Range("A15:C25"),Range("G13:H23"))
The above will give an error.


Nor can we do the following :

?Application.Intersect(Range("A15:C25"),Range("G13:H23")) = Nothing

[/pre]
What we need to do is :

?Application.Intersect(Range("A15:C25&#34,Range("G13:H23&#34) is Nothing[/code][/pre]
The answer will be : True


Hence , when you have a Worksheet_Change or Worksheet_SelectionChange event procedure , the first thing to do is to detect whether the event has taken place in a cell or cells which are of interest to us ; in your case , you wish to detect whether any change has taken place in E3.


Both of the above event procedures have a parameter available viz. Target , which has the contents of the affected cell ; Target.address will give you the address of this cell.


Application.Intersect used with two parameters , Target and Range("$E$3") , will return Nothing if the affected cell is not E3 , and will return the cell itself , as a Range object ; to verify this , enter the following in the Immediate window :

?Typename(Application.Intersect(Range("A15:C25&#34,Range("B13:D23&#34))[/code][/pre]
The answer will be : Range[/b]


The next line is to check whether E3 is blank or not ; the Worksheet_Change event is triggered even when you place the cursor in a cell , and press the Del[/b] key ; this will clear the contents of the cell ; this is what we are checking for by testing the Target against a VBA keyword vbNullString[/b]. The test will return TRUE if E3 is blank , and FALSE otherwise.


Narayan

 

[Smallman]

Gaurav


To add to Narayan's answer you might want to add;

Application.EnableEvents = False


before the interesct line and; 


Application.EnableEvents = true

before the end sub line.


This will stop the procedure triggering every time you push one of those lookups into your form (so an additional 47 odd iterations will be avoided.


This whole question is a real head scratcher for mine. Narayan has provided an excellent answer but why don't you just put the Vlookup formula in the form and protect the cells with the formulas in them??? That way any time that E3 is changed the form updates without any vb at all.


I am still scratching my head Gaurav.


Take care


Smallman

 

[gauravkrgautam]

thanks NARAYANK991 now it is clear for me you have explained it briefly. and now i am clear to use above coding and exlplain to others also. Also declaring hereby that all my concerns has been RESOLVED

thanks once again

thanks small man & NARAYANK991