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

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

Cheers

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

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

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

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

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

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

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.