How did you declare RawArray?

Did you mean to use:

Dim RawArray as Range
or
Dim RawArray as Variant 'to be used an array

This:
dim RawArray as variant
RawArray = mywrksht.Range("A2:AK" & CStr(VarA)).Value
makes RawArray an array of values--not an array. It's no longer a range.

This:
dim RawArray as Range
set RawArray = mywrksht.Range("A2:AK" & CStr(VarA))
(notice the Set Statement)
makes RawArray a range.

And if RawArray is a range, then rawarray.columns(1) should work ok.

If RawArray is an array, then the application.index() in the other post will
work fine (as you saw).

ps.
VBA is pretty darn forgiving:

You don't need the cstr() stuff:
set RawArray = mywrksht.Range("A2:AK" & VarA)
or
RawArray = mywrksht.Range("A2:AK" & VarA).Value
should both be ok (and easier to read!)


ker_01 wrote:

Dave & Bernie- Thank you to you both for your assistance. I wish I was half
as knowledgable as you guys.

Testing per the code suggestions,
application.match(tempid, rawarray.columns(1), 0)

didn't work, presumably because I was setting my range via
RawArray = mywrksht.Range("A2:AK" & CStr(VarA)).Value

and when I tried adding the "Set" statement per Dave's code
Set RawArray = mywrksht.Range("A2:AK" & CStr(VarA)).Value

I got a run-time error 13 (type mismatch). So rather than trying to
troubleshoot that, I followed the suggestion to use Index, and WOOT! It is
giving the desired result.

Thank you very, very much!
Keith

"Dave Peterson" wrote:

RawArray is more than one column and it's not really an array????

If it's a range:

res = application.match(tempid, rawarray.columns(1), 0)

if it's really an array with more than one column, you can use
application.index().

Dim RawArray As Variant
Dim res As Variant
Dim TempId As String

RawArray = ActiveSheet.Range("a1:e5").Value 'test data

TempId = 5 & "" 'make it a string

With Application
res = .Match(TempId, .Index(RawArray, , 1), 0)
End With

If IsError(res) Then
MsgBox "still no match"
Else
MsgBox res
End If


ker_01 wrote:

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


--

Dave Peterson


--

Dave Peterson