displaying error when a user provided value is not found in the excel

[vinay.sv]

Hi,
I'm having a userform where user will enter a query number to search in a list of 1000 queries.. Query number is alpha numeric & a unique number (eg. L01Q001, L01Q002, L02Q003) the lot number(L01,L02,L03,L04) repeats at times but the query id Q001, Q002 number is continues number and do not repeat and, i am able to find the query and display the query details using "Application.WorksheetFunction.Match", when searching with the existing query number (L01Q001) but when I give a non existing query number (L02Q001) instead of the message "Query unavailable" it throws error "Unable to get the match property of the WorkSheetFunction Class".. please help..

Private Sub Findquery()

Qid = Trim(queryid.Value)
If Qid = "" Then
msgbox "Enter a Query number to find in existing log"
Else
loc = Application.WorksheetFunction.Match(Qid, Range("B1:B1000"), 0)
If loc = 0 then
MsgBox("Query ID do not exist")
Exit sub
Else
DetailsForm.Qdesc = Cells(loc, 3).Value
DetailsForm.Qdate = Cells(loc, 4).Value
End If
End If

End Sub

Please Help...!!

 

[VoG]

Take a look here http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

Then try

Private Sub Findquery()
Dim Q1d As String, Loc As Variant
Qid = Trim(queryid.Value)
If Qid = "" Then
    MsgBox "Enter a Query number to find in existing log"
Else
    Loc = Application.Match(Qid, Range("B1:B1000"), 0)
    If IsError(Loc) Then
        MsgBox ("Query ID do not exist")
        Exit Sub
    Else
        DetailsForm.Qdesc = Cells(Loc, 3).Value
        DetailsForm.Qdate = Cells(Loc, 4).Value
    End If
End If
End Sub

 

[vinay.sv]

Thanks a Ton VoG...
I had my dose of Excel today , thanks for the link.. that made it easy to understand...