?
Solved

lookup in array vba

Posted on 2011-09-23
9
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 24

Accepted Solution

by:
StephenJR earned 800 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 93

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

777 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