lookup in array vba

Hi Guys,

I have a 2 column list in an array.  I want to lookup a value in the first column and return the value in the 2nd column.  I'm guess using a standard lookup.

Instead of looping through each item in the array to check to see if its the search string I'm looking for, is there a code that does it in a simple clean 1 or 2 line statement in VBA?

Who is Participating?
StephenJRConnect With a Mentor Commented:
Patrick's solution notwithstanding, I will just chuck in that you can do something like this (not invented by me):
Sub x()

Dim arr(1 To 2, 1 To 2)

arr(1, 1) = "fred"
arr(1, 2) = 3
arr(2, 1) = "george"
arr(2, 2) = 9

MsgBox arr(Application.Match("george", Application.Index(arr, 0, 1), 0), 2)

End Sub

Open in new window

Don't know of anything like that.  If you need to use this several times, create a function.  Otherwise, just write out the loop.  Do you need help with the loop?
Patrick MatthewsConnect With a Mentor Commented:
Instead of using an array, I would use a collection or a dictionary.  For example:

Dim dic As Object
Dim Counter As Long

Set dic = CreateObject("Scripting.Dictionary")
For Counter = 1 to 5
    dic.Add Counter, Counter * 2

MsgBox "3rd item: " & dic.Item(3)

Set dic = Nothing

Open in new window

For more about the dictionary:

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

kgerbConnect With a Mentor Chief EngineerCommented:
I have looked for this myself before.  I don't believe it exists.  However, one consolation may be that Excel is MUCH MUCH faster at looping through VBA arrays than it is ranges.  Just for fun I had excel fill an array with 1,000,000 numbers.  It took .019 seconds.  Unless you have an absolutely ginormous amount of looping to do I doubt you will even notice.  Anyway, there's my $0.02.

Patrick MatthewsCommented:

Thanks for posting that.  I figured Index would work, but in testing I couldn't get the syntax right :)

Rob HensonConnect With a Mentor Finance AnalystCommented:
Looping through the array would only be a few lines:

In plain speak rather than VBA language:

For each cell in range

If cell value = lookup value
R = Cell Row
Result = cell from next column
End If

Next Cell

If you need that putting into VBA language let us know.  A couple of questions before doing so:

Will the Range be selected or specified?
Will the lookup value be in a cell or input as part of routine?

Rob H
solarissfAuthor Commented:
thank you everyone for your input...

Kyle, good to know looping through arrays is extremely fast.

Ended up using StephenJR match/index line.  works like a charm.

I appreciate everyones suggestions and input.
solarissfAuthor Commented:
can someone clue me in... this worked the first time I used it.  now I'm trying to use same code in different project and I'm getting type mismatch


fxArray is 2 columns
MsgBox (fxArray(Application.Match(originalSymbol, Application.Index(fxArray, 0, 1), 0), 2))

Option Explicit

Public db As Database
Public rs1 As DAO.Recordset
Public sPath As String
Public fxArray As Variant

Sub Begin()

Dim originalSymbol As String
Dim revisedSymbol As String
Dim x As Integer

    sPath = "C:\Documents and Settings\x152169\Desktop\ClientProfitability.accdb"
    Set db = DBEngine(0).OpenDatabase(sPath, False, False)
    Set rs1 = db.OpenRecordset("ClientMapping")
    fxArray = rs1.GetRows(rs1.RecordCount)
    Set db = Nothing
    Set rs1 = Nothing
    For x = 2 To Range("A65536").End(xlUp).Row
        originalSymbol = Cells(x, 1).Value
        MsgBox (fxArray(Application.Match(originalSymbol, Application.Index(fxArray, 0, 1), 0), 2))

    Next x
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.