# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Vlookup in VBA?

## skuzapo

Hi,

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

Thanks for your help....

----------


## duane

you could use the find command to find the row in a given range and then refer to that location

for example

define tender from your form

Set rng = Range(a1:a100).Find(tender)
If rng Is Nothing Then GoTo some error default if no match
XXX=cells(rng.Row.1)

----------


## skuzapo

Hi - thanks for the suggestion - I'll try that out!

Cheers

----------


## skuzapo

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

----------


## JMB

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

----------


## skuzapo

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)

----------


## Rowan

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

----------


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

----------


## Dave Peterson

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").range("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

----------


## samcheung

I have a column of dates (column A) and the column B is the quantity (number ) respect to the dates. Now, I need to sum the total for each month. I would like to use the 'match' to find the location of last cell for each month so that I can use 'sum' to sum from the begining to the end of each month.
However, I found nothing using the following codes:
Dim res As Variant
res = Application.Match(CDate(1 / 8 / 2008), Worksheets("Year").Range("a1:a30"), 0)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox "found at pos: " & res
End If

Can anyone tell me where the problem is or tell me the smarter way to accomplish the task? thanks

----------


## samcheung

One more thing. When I am not using VBA, in the other words, I type this in a cell:

=match(date(2008,2,2), A1:A10)

the result comes out! but

compile error:
expected ) 

is shown with 2008 highlighted when I run the program.

----------

