Interesting- I went back and added (as a test, since it only does one match)
the vlookup from Bernie's post:

TempTest = Application.VLookup(TempID, RawArray, 5, False)

And it returned the correct/expected result, even though the
application.match still gives an error. So, that makes me assume that the
RawArray is also loaded properly, and that there is something else going on
that is beyond my comprehension.

8-/

Keith

"ker_01" wrote:

Hi Dave!

I checked the values by checking direct equivalence first, directly on the
worksheet:
=A2=Raw!A3128 [False]
and
=(A2&"")=Raw!A3128 [True]

I haven't changed any of the cell formatting, because this is an automated
dump that has to be processed monthly, so I felt it was easier just to
transform the number to a string in my code on the fly, rather than writing a
little more code to convert the whole column to string before processing.

Here is some additional information, because I still haven't detected what
is different about my code compared to the example Bernie provided (his
syntax for the application.match appears to be the same as what I'm using);

(in a loop)
TempID = ProArray(CheckC, 1) & ""
TempIDRow = Application.Match(TempID, RawArray, False) 'Returns error 2042
Debug.Print "." & TempID; "."
Debug.Print RawArray(3834, 1)

The first debug.print line shows .12954. (so I know there aren't leading or
trailing spaces; mouseover on TempID also shows "12954")
The second debug statement returns the string itself (12954) in the debug
window, and mouseover RawArray(3834, 1) in debug mode shows the value as
"12954", confirming that my workbook is storing the value as a string (as
expected).

Yet for some reason, the application.match is still returning an error :(

Thank you,
Keith


"Dave Peterson" wrote:

How did you manually confirm the match?

If you just looked at the values, that isn't enough.

If you just changed one of the cell's formatting from text to number/general (or
vice versa), that's not enough. (Re-enter the value after changing the
formatting.)

Did you actually compare the two cells that you thought matched, like:
=a1=x99

or did you create an =match() formula in a worksheet cell to make sure it
worked?

ker_01 wrote:

I googled, but didn't find any good hits that addressed this.

I have a 2-D array that is populated by combining unique records from two
different workbooks. I then have to use a value in the first column
(MyArray(X,1)) and use that to find a matching value in a different 2-D
array, pulled in from a different workbook ("RawArray"); once I know what
"row" the match is in, I will pull a value from the 5th "column" of RawArray.
I tried using application.match, but it keeps returning an error 2042. I've
manually confirmed that the value exists (correcting for one being text, the
other being a number), but it still isn't getting a match. I'm starting to
suspect that it is my syntax, or somehow related to how I load the "RawArray"
[in the code below] by using RawArray = mywrksht.Range("A2:AK5000").Value,
since it returns the values as RawArray(rows, columns) but I'm not clear on
whether that is really the problem, or how to fix it.

I appreciate any help,
Keith

Dataset 1 (expressed as a worksheet layout)

Header1 (Header2 Header 3, etc.)
ID101
ID102
ID103

Dataset2
Header1 H2 H3 H4 H5 (H6, etc)
ID412 abc
ID921 def
ID101 ghi

So basically, I am cycling the first list, and for each one pulling in the
corresponding value under header5, in this first case, ID101- ghi

Here is the actual code (sorry for the linewrap, I am using the MS web
interface, ugh):
For CheckC = 2 To LastPro
'changing the numeric value to a string, because it is a string in the
other array
TempID = ProArray(CheckC, 1) & ""
'try to find the matching value
TempIDRow = Application.Match(TempID, RawArray, False)
If IsError(TempIDRow) Then MsgBox "There is still a problem"
Next

--

Dave Peterson