Getting Details from 1 field

  • Thread starter Thread starter Ghell
  • Start date Start date
G

Ghell

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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
Click to expand...
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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


Ghell said:
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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
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
 
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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 
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
 
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 said:
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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
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 :
 
Back
Top