Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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




0
uad
Asked:
uad
  • 2
1 Solution
 
SANTABABYCommented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
I don't think the Application.Index function works on non Variant arrays. This should work:

    Dim x As Long
    Dim y As Long

    For x = 1 To RptRows
        For y = LBound(ProdArray, 1) To UBound(ProdArray, 1)
            If ProdArray(y, 1) = ReportArray(x, 1) Then
                ReportArray(x, 7) = ProdArray(y, 2)
                Exit For
            End If
        Next y
    Next x

Kevin
0
 
uadAuthor Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
You could do it that way. But looping through a few hundred elements of an array is pretty fast.

Kevin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now