Solved

lookup in array vba

Posted on 2011-09-23
9
315 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:solarissf
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 36587076
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?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 36587111
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
Next

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

Set dic = Nothing

Open in new window


For more about the dictionary:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 100 total points
ID: 36587135
solarissf,
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.

Kyle
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
LVL 24

Accepted Solution

by:
StephenJR earned 200 total points
ID: 36587184
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

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36587242
StephenJR,

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

Patrick
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36587260
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 36587269
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?

Thanks
Rob H
0
 

Author Comment

by:solarissf
ID: 36587304
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.
0
 

Author Comment

by:solarissf
ID: 36893727
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

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question