getting msg box for no match!

[via135]

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

 

[Tom Ogilvy]

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

 

[via135]

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









- 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

 

[Tom Ogilvy]

It worked fine for me.

--
Regards,
Tom Ogilvy

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

 

[via135]

It worked fine for me.

--
Regards,
Tom Ogilvy















- Show quoted text -

yes..it works when i open a new workbook and create a new user form!
but believe me it doesn't work when i edit the vb code in my earlier
workbook..!
i don't know the reason..! anyhow thks for the help!

btw one more help..! if there is same input value for successive
records for any given txtbox, is there any way to assign a macro to
function key for bring the previous value?

hope u understand..!

-via135

 

[Tom Ogilvy]

for finding multiple instances of a value, try using the help code for the
FindNext command rather than worksheet functions.

 

[via135]

for finding multiple instances of a value, try using the help code for the
FindNext command rather than worksheet functions.

--
Regards,
Tom Ogilvy










- Show quoted text -

sorry..i'm a novice to VBA
that's why asking for help
in this group!

regds!


-via135

 

[Tom Ogilvy]

that means go into the Visual Basic editor and going into Help. Then using
the help selection in the menu to find the command findnext. In the help
text will be sample code showing how to use code to fine multiple instances
of a value in a range.

So, it is giving you help.

 

[via135]

that means go into the Visual Basic editor and going into Help. Then using
the help selection in the menu to find the command findnext. In the help
text will be sample code showing how to use code to fine multiple instances
of a value in a range.

So, it is giving you help.

--
Regards,
Tom Ogilvy







- Show quoted text -

hi Tom!

i thk u misunderstood me!
my post is regarding inputting of values in a userfrom..!
and i need a shortcut to input the successive as well as
repetitve input values..!..like ^D is xl's WS function..???!!!

hope u understand..!!??

-via135

 

[via135]

hi Tom!

i thk u misunderstood me!
my post is regarding inputting of values in a userfrom..!
and i need a shortcut to input the successive as well as
repetitve input values..!..like ^D is xl's WS function..???!!!

hope u understand..!!??

-via135- Hide quoted text -

- Show quoted text -

Dave!

help me pl?

-via135