Searching values in VBA

[Pierre Leclerc]

Hi

I use a lot of INDEX/MATCH in my workbooks and I am looking for an
equivalent to INDEX/MATH or VLOOKUP in VBA.

When I have large sets of data I create Variant Arrays and I would
need a way to VLOOKUP things in one array to bring values in the other
array.

Anyone?
Pierre Leclerc
http://www.excel-vba.com
(e-mail address removed)

 

[SkipVought]

Pierre,

Here's an answer i posted recently. It seems to fit you
question as well.

I like using Named Ranges. I try to design my Tables in
Excel using the following rules

List/table is isolated from other data
List/table is contiguous (no empty columns/rows)
List/Table has ONE ROW of unique headings

Under these conditions I use the CurrentRegion Icon to
SELECT the entire list/table and then Insert/Name/Create-
Create names in TOP row.

I then use column range names in all my worksheet
formulas and in my VBA code.

Sub LoadMasterTable
Dim r as range, sItem as string, ptr as variant
Dim wsMaster as worksheet, wsPrice as worksheet
Set wsMaster = Sheets("Master Table")
Set wsPrice = Sheets("Price Table")
With wsMaster
For Each r in .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
With r
sItem = .Value
ptr = application.match(sItem, wsPrice .Range
("Price_Table_Item"),0)
If Not IsError(ptr) Then
.Offset(0, 1).Value = application.index(range
("Price_Table_Price"),ptr, 1)
End If
End with
Next
End With
End Sub