Error Handler [VBA]

[vijay.vizzu]

Hi all,

Below is my code, its working fine, but one problem is that,i can't able to put error handler like in my case, if vendor code not found in vendor details sheet, then it should display a message "Vendor code not found". Please helpe me to resolve this.

[pre]

Sub venAddress()

Dim findVenCD As Integer 'Inputted vendor code
Dim vendName As String 'To fetch vendor name from vendor database
Dim venCD As Integer 'Find vendor code & retrive the data from vendor database
Dim venAdd As String 'To fetch vendor address
Dim genPO As String

Application.ScreenUpdating = False
findVenCD = [vendcd] 'Inputted vendor code

Worksheets("vendor_details").Activate
venCD = WorksheetFunction.Match(findVenCD, Range("b:b"), 0) 'To match inputted vendor code in VendorDB
vendName = WorksheetFunction.Index(Range("c:c"), venCD, 0) 'To display vendor name in the range
venAdd = WorksheetFunction.Index(Range("d:d"), venCD, 0) 'To display vendor address in the range
genPO = "IYM/PROTO/010/" & Worksheets.Count - 3 + 1 'To create sequence number

[vendcd] = findVenCD 'puts inputted vendor code in the range
[venName] = vendName 'puts vendor name in the range
[venAddr] = venAdd 'puts vendor address in the range
[poNo.] = genPO 'puts sequence number
[poDate] = Now() 'puts current date

Sheets("Template").Activate
Application.ScreenUpdating = True
End Sub

[/pre]

 

[shrivallabha]

Hi Vijay,


Try to wrap error like this. Note that instead of worksheetfunction, I have used application to trap error.

[pre]

If IsNumeric(Application.Match(findVenCD, Range("B:B"), 0)) Then
venCD = WorksheetFunction.Match(findVenCD, Range("B:B"), 0)
Else
MsgBox "Specified Vendor Code is not available!", vbExclamation
End If

[/pre]

Please read this article by Chip Pearson for using Application:

http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx

 

[vijay.vizzu]

Thank you shrivallabha, let me try this, but as per your code, you have used Isnumeric, it will only check whether the input is number or not, if it is number then it will go on other it will show message. By this, it won't check whether the input value is exist in the vendor details or not. I want if the input value is not found in the vendor details then it should display a message.


I hope you got my point


Vijay

 

[Debraj]

Hi Vijay..

Dont worry.. Match will always return you Number or nothing..


If you are assuming that FIND ISNOTHING is same MATCH ISNUMBER.. then you are going in wrong direction..


Try FIND ISNOTHING also.. (Google it)..


Regards,

Deb

 

[kaushik03]

Hi Vijay ,


I am not very sure about your entire data set but here is my try:


http://speedy.sh/eRRKT/Vijay-test.xlsm


In vendor details sheet we have vendor name and code.


In search sheet we have vendor name only. According to your problem, we want vendor code to be returned if found in vendor details sheet, if not found then throw a message to user.


I have done it in a very generic way. If you find this is useful you can adapt the same to your need.


Check module1 and run the code line by line(by pressing F8). In A2 of search sheet, put any letter except "a" to "h" and test the code.


Kaushik

 

[Dhamo]

'Vijay Try this.


Sub venAddress()


Dim findVenCD As Integer 'Inputted vendor code

Dim vendName As String 'To fetch vendor name from vendor database

Dim venCD As Integer 'Find vendor code & retrive the data from vendor database

Dim venAdd As String 'To fetch vendor address

Dim genPO As String


Application.ScreenUpdating = False

findVenCD = 4 '[vendcd] 'Inputted vendor code


Worksheets("vendor_details").Activate

On Error GoTo ErrHndlr


venCD = WorksheetFunction.Match(findVenCD, Range("b:b"), 0) 'To match inputted vendor code in VendorDB

vendName = WorksheetFunction.Index(Range("c:c"), venCD, 0) 'To display vendor name in the range

venAdd = WorksheetFunction.Index(Range("d:d"), venCD, 0) 'To display vendor address in the range

genPO = "IYM/PROTO/010/" & Worksheets.Count - 3 + 1 'To create sequence number


[vendcd] = findVenCD 'puts inputted vendor code in the range

[venName] = vendName 'puts vendor name in the range

[venAddr] = venAdd 'puts vendor address in the range

[poNo.] = genPO 'puts sequence number

[poDate] = Now() 'puts current date


Sheets("Template").Activate

Application.ScreenUpdating = True

Exit Sub


ErrHndlr:

MsgBox "Error found - Reasons may be:" & vbCrLf & "1. VendorID not found" & vbCrLf & "2. " & Err.Description, vbCritical, "Vijay"


Exit Sub


End Sub