Solved

lookin in array not working match/index

Posted on 2011-09-30
11
331 Views
Last Modified: 2012-05-12
Hi guys,

I loaded a 2 column array from Access.  Someone here gave me this code using match/index to lookup and return the value in the next column of the array.  But when I use it on a new project it doesn't work.  Driving me crazy.  I get the error messaeg type mismatch

Just for background info, when I use a WATCH for fxARRAY this is what it looks like
fxArray     Variant/Variant(0,1),(0 to 80)
   fxArray(0)
  fxArray(1)

so the array is basically 2 columns with 80 rows.

anyideas why the match/index line is not working???

thanks!!!
Option Explicit
Option Base 1
Public db As Database
Public rs1 As DAO.Recordset
Public sPath As String
Public fxArray As Variant

Sub Begin()

Dim originalSymbol As Variant
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)

Open in new window

0
Comment
Question by:solarissf
11 Comments
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Wrong zone.

This is the Visual Basic .NET zone, and your code is VB6/VBA.  VB.NET is very different, specially with the type of code you are using.
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
Hi,

What values are you using to match - ie what are the values in cells(2,1) onwards?

0
 

Author Comment

by:solarissf
Comment Utility
All strings
0
 
LVL 17

Expert Comment

by:andrewssd3
Comment Utility
The code you have posted doesn't make sense to me - I'm assuming it runs from Access, but then for the loop it appears to switch into Excel - Access does not have a Range or an Application.Match method.  Excel does, but you could not use an array as an argument to Match - it would have to take an Excel range.

Could you clarify what you are trying to do and from what application?  Maybe posting the whole VBA code would help
0
 

Author Comment

by:solarissf
Comment Utility
Yes, thank you. I will post the whole code tonight. Basically this starts by connecting to access, and loading the table into an array. Then I am trying to do a lookup through the array.  I know I can go through each item in array, but I was trying to do the fancy one liner that uses index matching.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
I had difficulties making the fxArray(match(index),2) work with the test data I developed for this example.  Perhaps your table structure is different, but 2 columns where 1 is symbol and 2 is a reference you're trying to obtain via that symbol (like a vlookup against the array) didn't work for me with that statement.

I'm assuming your table is formatted as follows:

Symbol, Name/Reference  (for example)
CVX, Chevron
XOM, ExxonMobile

Given that format, this is how I would structure the code:

 
Option Explicit
Option Base 1
Public db As Object 'database?
Public rs1 As DAO.Recordset
Public sPath As String
Public fxArray As Variant, symbArray As Variant

Sub Begin()

Dim originalSymbol As Variant
Dim revisedSymbol As String
Dim x As Integer

    sPath = ThisWorkbook.Path & "\ClientProfitability.accdb" '"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
    
    'Get the symbol-related dimension of the 2-dimensional array, fxArray
    ReDim symbArray(0 To UBound(fxArray, 2)) As Variant
    For x = LBound(fxArray, 2) To UBound(fxArray, 2)
        'Use this code when the symbol is the first column of the table
        symbArray(x) = fxArray(0, x)
        'Use this code when the symbol is the second column of the table
        'symbArray(x) = fxArray(1, x)
    Next x
    
    For x = 2 To Range("A65536").End(xlUp).Row
        originalSymbol = Cells(x, 1).Value
        
        'Original code: MsgBox fxArray(Application.Match(originalSymbol, Application.Index(fxArray, 0, 1), 0), 2)
        'if the table of 2 columns has the first column as symbol, then reference then use this statement
        MsgBox fxArray(1, Application.Match(originalSymbol, symbArray, 0) - 1)
        'if the table of 2 columns has the first column as reference, then symbol then use this statement
        'MsgBox fxArray(0, Application.Match(originalSymbol, symbArray, 0) - 1)
        
    Next x
    
End Sub

Open in new window


If your data is structured differently from this, please provide a brief example, and I can calibrate my code to that - and perhaps the original statement would make more sense to me.  Read on, if you're interested in my diagnosis of the match/index statement and problems that I saw...
----------------------------------------------------------------------------------------------------------------
The MATCH function has 3 parameters - match(lookup_Value,Lookup_Array,match_type)

So let's focus on the Lookup_Array - that should be an array, not a single parameter.  I assume you're trying to show fxArray such that the first dimension is held constant at 0.  e.g., fxArray(0,1) is the first symbol, fxArray(0,2) is the second symbol, etc.  Index returns a value based on a row/column combination.  Index does not return an array - so puzzling how this could have worked in another example...

So, to get that one-dimensional array, we need to create one, slicing the 2 dimensional array apart, element by element (alternatively, read the database again and getRows on the symbol-related field)
------------------------------------------------------------------------
Application.Index(fxArray, 0, 1) is looking at row 0, column 1 of fxArray - wouldn't the INTENT be the same as:  fxArray(0,1)?  Regardless of whether the Index function works against the array, you're adding overhead to your app by using it, when you can just reference the array directly, and index it as you would any other array, like fxArray(0,1)
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
If you MUST use index/match against fxArray, this will work (note slight modification of the statement in the last loop):

 
Option Explicit
Option Base 1
Public db As Object 'database?
Public rs1 As DAO.Recordset
Public sPath As String
Public fxArray As Variant, symbArray As Variant

Sub Begin()

Dim originalSymbol As Variant
Dim revisedSymbol As String
Dim x As Integer

    sPath = ThisWorkbook.Path & "\ClientProfitability.accdb" '"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
    
    'Get the symbol-related dimension of the 2-dimensional array, fxArray
    ReDim symbArray(0 To UBound(fxArray, 2)) As Variant
    For x = LBound(fxArray, 2) To UBound(fxArray, 2)
        'Use this code when the symbol is the first column of the table
        symbArray(x) = fxArray(0, x)
        'Use this code when the symbol is the second column of the table
        'symbArray(x) = fxArray(1, x)
    Next x
    
    For x = 2 To Range("A65536").End(xlUp).Row
        originalSymbol = Cells(x, 1).Value
        
        'Original code: MsgBox fxArray(Application.Match(originalSymbol, Application.Index(fxArray, 0, 1), 0), 2)
        
        'if the table of 2 columns has the first column as symbol, then reference then use this statement
        
        MsgBox Application.Index(fxArray, 1, Application.Match(originalSymbol, symbArray, 0))
        
        'MsgBox fxArray(1, Application.Match(originalSymbol, symbArray, 0) - 1) 'or use this
        
        'if the table of 2 columns has the first column as reference, then symbol then use this statement
        'MsgBox fxArray(0, Application.Match(originalSymbol, symbArray, 0) - 1)
        
    Next x
    
End Sub

Open in new window


This also works just fine with the dummy access table I created.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Line 38, above should read:  MsgBox Application.Index(fxArray, 2, Application.Match(originalSymbol, symbArray, 0))


Sorry, I should have posted my working files.  There's data in here to play with, so it will all make sense.

See attached the dummy access database I created for ClientMapping - a takeoff on oil stocks using stock symbol and company name
Spreadsheet with list of stock symbols where the app sends messagebox messages to name each of the companies, based on the stock symbol data.


Save both to the same directory, then open the .xlsm file and run/test/whatever.

Let me know if this works for you and by application to your dataset, if there are any difficulties.

Cheers,

Dave
ClientProfitability.accdb
clientMapping-r1.xlsm
0
 

Author Comment

by:solarissf
Comment Utility
thank you.  The biggest thing I didnt realize was that index would only work on a 1-column array.
This has solved my issue.
appreciated!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Index indeed DOES work on a 2-dimensional array, hence:

Application.Index(fxArray, etc.....  <- fxArray is a 2-d array

However, MATCH doesn't work with a 2-dimensional array.  We use MATCH to find a lookup value along one dimension.  The problem is locking in that dimension from within VBA. I've tried with E-E help to do that in the past, and have as yet to develop a solution which has a 2-d array present itself along one dimension so you can use functions like MATCH against it.  It eventually boils down to iterating along that dimension to create a new 1-dimensional array which can be used in successive operations.

Dave
0
 

Author Comment

by:solarissf
Comment Utility
sorry, thanks for the clarification.   MATCH not working with 2D
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now