Thanks Patrick,
Yes Application.WorksheetFunction.Match needs error trap,
But Application.Match is different from Application.WorksheetFunction.Match.
Application.Match doesn't raises an error, just return a value of the error.
I tend to use this one. :)
--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)
URL:
http://www.interq.or.jp/sun/puremis/...astersLink.htm
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
"Patrick Molloy" wrote in message
...
This code will fail if the item isn't found. The functian
raises as error which you must trap.
Use
On Error Resume Next
'' you Application.WorksheetFunction.Match
IF Err.Number<0
Err.Clear
End If
On Error GoTo 0
Hint: I always call these functions within my own
function that avoids re-setting my error handlers, plus,
my function will always return a value...I can decide
what if there's no match, eg False, zero, -1, depending
on teh reason for the lookup
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hi Ghell,
Here is another way using "Application.Match".
Dim ID As Long, ret
ID = InputBox("Please Input ID")
ret = Application.Match(ID, Columns(1), 0)
If Not IsError(ret) Then MsgBox Cells(ret, 2).Value
--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/_/_/
Colo of 'The Road of The Cell Masters' :)
URL:http://www.interq.or.jp/sun/puremis/colo/CellMastersL
ink.htm
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/_/_/
"Ghell" wrote in
message
...
well, i do a lot of ASP, so i got very confused with
VBA but basicly im
trying to get it so that the user enters in the ID
number of a row that
looks like this:
ID Firstname Lastname
1 John Smith
2 etc etc
so they would enter in "1" and "John" would appear as
a result
i could do this with a for loop going down the whole
id column(A) and
check against the variable that i have from the
enteered ID i suppose
but there has to be a better way
This is my first piece of VBA but i know a little VB
and a lot of
ASP(which is almost similar:p) so don't think im
stupid just because I
can't do this relatively simple piece of VBA
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/
~~Now Available: Financial Statements.xls, a step by
step guide to
creating financial statements
.