Solved

lookup in array vba

Posted on 2011-09-23
9
307 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 32

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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