asked on

MS Excel - Array MATCH type mismatch

Continuation of prior question - "elegant" solution is generating at type mismatch on the match function of an array item within an array. The goal is to build an new array for a report extracting the necessary data from various existing arrays in various column locations. Also, the column that is being 'matched' is the first column in each of the arrays.

For x = 1 To RptRows
' (error is generated here)
ID_Fnd = Application.Match(ReportArray(x, 1), Application.Index(ProdArray, , 1), 0)
ReportArray(x, 7) = ProdArray(ID_Fnd, 2)
Next x

Watch is showing the following:
ReportArray(x,1) - variant/long
Application.Index(ProdArray, , 1) - variant/variant

SANTABABY

Could you please supply some surround code?
I'm curious to see how ReportArray and ProdArray are defined?
Would be great if you can attach a sample spreadsheet with the code & data where the problem occurs.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

uad

ASKER

I will try this and get back to you.

From a resource and speed perspective - rather than looping through to find ReportArray(x,1) value for all the rows, would it make more sense to create a MatchArray with only the ID in the identical sequence as the ProdArray and still use the Match (& Index) as was first suggested?

ReDim ProdArray(xRows, 2)
Dim MatchArray(xRows)
Range("A1").Select
For x = 1 To SchCPRows
ProdArray(x, 1) = ActiveCell.Value
MatchArray(x) = Activecell.value
ProdArray(x, 2) = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Next x

Dim ID_Fnd%
For x = 1 To xRows
ID_Fnd = Application.Match(ReportArray(x, 1), MatchArray, 0)
ReportArray(x, 7) = ProdArray(ID_Fnd, 2)
Next x

zorvek (Kevin Jones)

You could do it that way. But looping through a few hundred elements of an array is pretty fast.

Kevin