Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

lookup in array vba

Posted on 2011-09-23
9
Medium Priority
?
354 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

604 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