Vlookup in VBA?
 

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

Vlookup in VBA?
 

Hi again,
I've tried the code below which causes an error on the FileName line...
The message is "subscript out of range".

Any suggestions?



Private Sub txtItem1_AfterUpdate()

Dim Item As Integer
Dim FileName As String
Dim MyRange As String

FileName = "k:\downloaded std costs\itemlist.xls"
MyRange = "b1:b9999"

Item = txtItem1.Value
Set Rng = Worksheets(FileName).Range(MyRange).Find(Item)

If Item < "" Then
txtItem2.Visible = True
Else
txtItem2.Visible = False
End If

End Sub


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

Vlookup in VBA?
 

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

Vlookup in VBA?
 

You have set the FileName variable to be the name and path of a file but
are then using it in a sheet name:
Set Rng = Worksheets(FileName).Range(MyRange).Find(Item)

You need a variable called SheetName = to the name of your sheet eg:
Dim ShtName as string
ShtName = "Sheet1"
Set Rng = Worksheets(ShtName).Range(MyRange).Find(Item)

Or you can use a sheet object eg:
Dim mySht as worksheet
Set mySht = sheets("Sheet1")
Set Rng = mySht.Range(MyRange).Find(Item)

Hope this helps
Rowan

skuzapo wrote:
Hi again,
I've tried the code below which causes an error on the FileName line...
The message is "subscript out of range".

Any suggestions?



Private Sub txtItem1_AfterUpdate()

Dim Item As Integer
Dim FileName As String
Dim MyRange As String

FileName = "k:\downloaded std costs\itemlist.xls"
MyRange = "b1:b9999"

Item = txtItem1.Value
Set Rng = Worksheets(FileName).Range(MyRange).Find(Item)

If Item < "" Then
txtItem2.Visible = True
Else
txtItem2.Visible = False
End If

End Sub

Vlookup in VBA?
 

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

Vlookup in VBA?
 

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

Vlookup in VBA?
 

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