It worked fine for me.

--
Regards,
Tom Ogilvy


"via135" wrote in message
oups.com...
On Feb 24, 10:36 am, "Tom Ogilvy" wrote:
Dim res as Variant

res = Application.Index(Worksheets("db").Range _
("B:B"), Application.Match(CLng(txtMicr.Text), _
Worksheets("db").Range("A:A"), 0))
if iserror(res) then
msgbox "No Match"
else
ws.Cells(iRow, 1).Value = res
end if

--
Regards,
Tom Ogilvy

"via135" wrote in message

oups.com...



hi all!

if there is no match available for the following code i want a msg box
"No Match" and alternately i should be able to input any text
value ..!
is it possible?!

ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _
("B:B"), Application.Match(CLng(txtMicr.Text),
Worksheets("db").Range("A:A"), 0))

help pl?

regds!

-via135- Hide quoted text -

- Show quoted text -

still i am not getting the error msg box if there is no match!
this is my code..


Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a micr number
If Trim(Me.txtMicr.Value) = "" Then
Me.txtMicr.SetFocus
MsgBox "Please enter a Micr number"
Exit Sub
End If

'add records to database

Dim res As Variant
res = Application.Index(Worksheets("db").Range _
("B:B"), Application.Match(CLng(txtMicr.Text), _
Worksheets("db").Range("A:A"), 0))
If IsError(res) Then
MsgBox "No Match"
Else
ws.Cells(iRow, 1).Value = res
End If
ws.Cells(iRow, 2).Value = Me.txtChqno.Value
ws.Cells(iRow, 3).Value = Me.txtAmount.Value


'clear the data
Me.txtMicr.Value = ""
Me.txtChqno.Value = ""
Me.txtAmount.Value = ""
Me.txtMicr.SetFocus

End Sub


help pl?

regds!

-via135