asked on

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?

thanks

rfportilla

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?

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

StephenJR,

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

Patrick

StephenJR

Found a link to a page which sets this out: http://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Forero

ASKER

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.

Stephen Forero

ASKER

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

using

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)
    rs1.Close
    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