Hi,

Is there an equivalent function in VBA to find something in a list
based on input in a form?

Thanks for your help....


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=470110

If the list is stored in a worksheet range or an array variable, you should
be able to use VLOOKUP in vba by

x=Application.VLookup(arg1, arg2, arg3, arg4)


"skuzapo" wrote:


Hi,

Is there an equivalent function in VBA to find something in a list
based on input in a form?

Thanks for your help....


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=470110

Hi JMB - thanks for that,

So if the data is in a worksheet and there are over 10000 lines to tes
I need to use a range.

How do I do that? Do I need to include the variable at the end which i
this case is called "Item"?

x=Application.VLookup(worksheets("itemlist").range ("b1:b10000"),Item

--
skuzap
-----------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...fo&userid=2743
View this thread: http://www.excelforum.com/showthread.php?threadid=47011

It's just like the =vlookup() formula in a cell:

=vlookup(a1,itemlist!b:b,1,false)

(although, if you're just checking for a match, =match() seems more fitting.)
=match(a1,itemlist!b1:b1000,0)

In code:

dim res as variant
res = application.match(myItem,worksheets("itemlist").ra nge("b:b"),0)

if iserror(res) then
msgbox "not found"
else
msgbox "found at pos: " & res
end if

(Any reason not to use the whole column?)

skuzapo wrote:

Hi JMB - thanks for that,

So if the data is in a worksheet and there are over 10000 lines to test
I need to use a range.

How do I do that? Do I need to include the variable at the end which in
this case is called "Item"?

x=Application.VLookup(worksheets("itemlist").range ("b1:b10000"),Item)

--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=470110

--

Dave Peterson

Is that list a single column or single row?

dim res as variant
dim myList as variant
mylist = array("abc","def","ghi")
res = application.match(me.textbox1.value,mylist,0)

if iserror(res) then
msgbox "not found"
else
msgbox "found at pos: " & res
end if



skuzapo wrote:

Hi,

Is there an equivalent function in VBA to find something in a list
based on input in a form?

Thanks for your help....

--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=470110

--

Dave Peterson