Application.Match on 2D array - syntax problem?

[ker_01]

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

 

[Bernie Deitrick]

Keith,

This is the way I would do it, to pull the value from column E after
matching the data in column A.

Dim RawArray As Range
Set RawArray = Sheets("Whatever").Range("A1:A5000")
TempID = "Find this ID test"
TempIDRow = Application.Match(TempID, RawArray, False)
MsgBox Cells(TempIDRow, 5).Value

Unless you need to pull multiple data values from that row, VLOOKUP would
work as well:

Dim RawArray As Range
Set RawArray = Sheets("Whatever").Range("A1:E5000")
TempID = "Find this ID test"
Msgbox Application.Vlookup(TempID, RawArray, 5, False)


HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

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?

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

 

[ker_01]

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

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?

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

 

[ker_01]

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

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

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?

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

 

[Bernie Deitrick]

Keith,

Match will only work on a single dimension range - one row or one column.
So you could use

TempIDRow = Application.Match(TempID, RawArray.Columns(1), False)

VLOOKUP will work since it takes a two dimension array as an argument.


HTH,
Bernie
MS Excel MVP

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

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

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]

I'm not sure how you create rawarray.

But maybe this will help:

Dim res as variant
dim RawRng as range
with worksheets("Raw")
set rawrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

'inside your loop
TempID = ProArray(CheckC, 1) & ""
res = application.vlookup(tempid, rawrng,0)
if iserror(res) then
'not found
else
'found
end if
====
But you didn't report whether you got the =match() worksheet formula working in
a cell in the worksheet.

=match(A2&"",raw!a:a,0)

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

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?

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]

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

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

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

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

 

[ker_01]

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

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

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

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


:

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]

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!)

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

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

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

:

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


:

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