well, i do a lot of ASP, so i got very confused with VBA but basicly i
trying to get it so that the user enters in the ID number of a row tha
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) an
check against the variable that i have from the enteered ID i suppos
but there has to be a better way

This is my first piece of VBA but i know a little VB and a lot o
ASP(which is almost similar:p) so don't think im stupid just because
can't do this relatively simple piece of VB

-----------------------------------------------
~~ 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

Use the VLOOKUP function
This allows you to look for an item in the first column
of a table, then return the value from aother column.
You could also use the Match then Index function.
Careful with VLookup as it raises an error where the item
isn't in the list.

On Error Resume Next
result = _
Application.WorksheetFunction.VLookup
(FindWhat,Table,col,False)
If Err.Number<0 then
Err.Clear
Result="Nothing Found"
End If
On Error goto 0


Patrick Molloy
Microsoft Excel MVP
-----Original 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
.

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/...astersLink.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

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

.

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

.

anyone notices how that original message thing is incredibly annoying?
anyway.. im still confused.. dont shout at me but can some1 give me
full macro code or something for it? as i said i am very new to VBA an
i dont really konw how most of tese objects wor

-----------------------------------------------
~~ 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

Colo's code basically does what you asked.

Sub Tester1()
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
else
msgbox "Not Found"
end if
End Sub

--
Regards,
Tom Ogilvy


"Ghell" wrote in message
...

anyone notices how that original message thing is incredibly annoying?
anyway.. im still confused.. dont shout at me but can some1 give me a
full macro code or something for it? as i said i am very new to VBA and
i dont really konw how most of tese objects work


------------------------------------------------
~~ 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

yea, thanks colo, lil canges and i cna get it doing what i whant exactl
but i didnt think it would work when i saw it first :p

ill try toms before i modify it

but anyqway.. thanks all :

-----------------------------------------------
~~ 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