Help with Type Mismatch Error on Match

[ELanc]

Hi,
I have a VBA program for a large file, which works correctly but is slow.
I searching the internet I have see that working off an array vs. the worksheet drastically improves performace. I began converting my code to load my data into an array but am running into a problem with a couple code lines where I was using Function.Match to pull row numbers to create a sub-range of the larger data set.

Below has the parts of my code relevant to where I am having trouble. I keep getting Run-time error 13 - type mismatch. See the first two lines in the For Each loop.

I have tried Appplication.Worksheetfunction.Match - same problem.
I have tried creating an array of just the first column - same problem.
If I specify the array element that has the matching contract number (i.e. ArrQData(2,1) the match will work, but obviously that does not acheive what I want. Can you not use Match with a VBA array?

Please help! The contract numbers are either numeric or Alpha-numeric.
Also, I have about 350,000 rows of data, but my understanding is that row limits no longer apply???? - only limit is memory?

In advance of anyone asking... I cannot upload my file - partly due to file size but also confidential data.

Sub SearchContracts()
[INDENT]Dim wksContracts As Worksheet, wksQData As Worksheet
Dim ArrQData() As Variant
Dim QDataRows As Long, QDataCols As Long
Dim CntFrstRow As Long, CntLastRow As Long
Dim ContractList As Range, CntSrchRng As Range
  
Set wksContracts = ThisWorkbook.Sheets("Contract List")
Set wksQData = ThisWorkbook.Sheets("Query Results")
Set ContractList = wksContracts.Range("A1", wksContracts.Cells(Rows.Count, 1).End(xlUp))
QDataRows = wksQData.Cells(Rows.Count, 1).End(xlUp).Row
QDataRows = wksQData.Cells(1, Columns.Count).End(xlToLeft).Column
ArrQData = wksQData.Range("A1", wksQData.Cells(QDataRows, QDataCols))[/INDENT]
 
[INDENT]For Each Contract In CntList
[INDENT]CntFrstRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 0)
CntLastRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 1)
Set CntSrchRng = 'creates a subset of the array - range of "A & CntFrstRow" thru "N & CntLastRow"
Call FillBnftHist(CntSrchRng) 'manipulates the data in the range and outputs to a new file.[/INDENT]

Next Contract[/INDENT]
End Sub

 

[Hui]

Match is an Excel worksheet function and unfortunately you can't use it on a VBA Array

You will need to write your own code to replicate the function

Having said that there are lots of VB/VBA resources on the net and so doing a Google search will undoubtedly find you a replacement quickly

 

[NARAYANK991]

Hi ,

I am not sure what you are trying to achieve with the 2 MATCH statements , since I do not have your data layout.

However , I did try with some data , and the following code works ; there are some significant changes from your code , and that may probably be the reason for the error with your code. Adapt this to suit your file and see if it works.

Sub SearchContracts()
    Dim ArrQData As Variant
    Dim QDataRows As Long, QDataCols As Long
    Dim CntFrstRow As Long, CntLastRow As Long
    Dim ContractList As Range, CntSrchRng As Range
 
    Set wksContracts = ThisWorkbook.Sheets("Sheet1")
    Set wksQData = ThisWorkbook.Sheets("Sheet2")
    Set ContractList = wksContracts.Range("A1", wksContracts.Cells(Rows.Count, 1).End(xlUp))
    QDataRows = wksQData.Cells(Rows.Count, 1).End(xlUp).Row
    QDataCols = wksQData.Cells(1, Columns.Count).End(xlToLeft).Column
    ArrQData = wksQData.Range("A5", wksQData.Cells(QDataRows, QDataCols)).Value
    For Each Contract In ContractList
        CntFrstRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 0)
        CntLastRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 1)
        Set CntSrchRng = Range("A" & CntFrstRow & ":" & "N" & CntLastRow)
        Call FillBnftHist(CntSrchRng) 'manipulates the data in the range and outputs to a new file.
    Next Contract
End Sub

Narayan

 

[ELanc]

Thanks. It looks like the difference is really loading the array with .values rather than just the wksQData range? I know it's difficult without seeing the data so thanks for trying attempting a test. I'll give it a try.

Basically what I have is two worksheets "Contract List" & "Query Data". "Contract List" is just that a 1 column list of contract numbers (numeric or alpha-numeric). "Query Data" contains transactional information for each contract. There can be one to many rows of transactions for each contract -350K rows in total.

My Sub FillBenefitHistory takes the Query Data rows for each contract and does some data manipulation and consolidates the history trasactions and then outputs to a new worksheet. The two match statements were to bookend the rows pertianing to that contract to create as subrange for FillBenefitHistory to work with.

I have a working macro but it takes close to a full hour to process so I was trying to change everything to do the processing within arrays and then output to the new sheet at the end.... but easier said than done I guess. I'm kind of new to VBA, especially arrays. I'll let you know how your suggestion work out.

 

[ELanc]

Hi ,

I am not sure what you are trying to achieve with the 2 MATCH statements , since I do not have your data layout.

However , I did try with some data , and the following code works ; there are some significant changes from your code , and that may probably be the reason for the error with your code. Adapt this to suit your file and see if it works.

Sub SearchContracts()
    Dim ArrQData As Variant
    Dim QDataRows As Long, QDataCols As Long
    Dim CntFrstRow As Long, CntLastRow As Long
    Dim ContractList As Range, CntSrchRng As Range
 
    Set wksContracts = ThisWorkbook.Sheets("Sheet1")
    Set wksQData = ThisWorkbook.Sheets("Sheet2")
    Set ContractList = wksContracts.Range("A1", wksContracts.Cells(Rows.Count, 1).End(xlUp))
    QDataRows = wksQData.Cells(Rows.Count, 1).End(xlUp).Row
    QDataCols = wksQData.Cells(1, Columns.Count).End(xlToLeft).Column
    ArrQData = wksQData.Range("A5", wksQData.Cells(QDataRows, QDataCols)).Value
    For Each Contract In ContractList
        CntFrstRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 0)
        CntLastRow = Application.Match(Contract, Application.Index(ArrQData, 0, 1), 1)
        Set CntSrchRng = Range("A" & CntFrstRow & ":" & "N" & CntLastRow)
        Call FillBnftHist(CntSrchRng) 'manipulates the data in the range and outputs to a new file.
    Next Contract
End Sub

Narayan

Hi,
The code change you suggested didn't work, but I wanted to pass along that I figured out the problem.

Apparently the using MATCH in an array is still subject to the old 65,536 row limit even though XL 2007 no longer has that limit. If I loaded my array with QDataRows hard coded to 65536 it worked fine. If I set QDataRows to 65537 then I'm back to the type mismatch error.

I'll guess have to determine a different way to isolate the rows for each contract in the array as I loop through each contract.

 

[Marc L]

Hi,

use Find method …
​