Forum Discussion
Getting an error of "error type mismatch" for index match code
I have created a simple index match vba code but unfortunately it is showing an error: "unable to get match property of worksheetfunction class" and thus decided to include CLng into the index match code but am getting an error: "error type mismatch"
The data that i am looking up contains a long string of numbers (10 - 16).
But advise how do i go about this? This is my code below:
Sub idAcc()
Dim ws As Worksheet
Dim agingwb As Workbook
Set ws = Sheet1
Set agingwb = Workbooks.Open("V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\3. Monthly Combined Aging\2022\Jun22\Corp_Aging_6Year_Splitv2_2022-06-02.xlsx")
Set wb = ActiveWorkbook
ThisWorkbook.Activate
ws.Range("AA1").EntireColumn.Insert
ws.Range("AA1").Value = "Asset_Status"
ws.Cells(27, 2).Value = Application.WorksheetFunction.Index(agingwb.Sheets("details_open_amt_by_acc").Range("A:A"), Application.WorksheetFunction.Match(Cells(2, 1), CLng(agingwb.Sheets("details_open_amt_by_acc").Range("E:E")), 0))
End Sub
Thanks in advance.
3 Replies
First, the conversion to type Long (CLng) was unnecessary and misguided.
And more to the point, its use in that context creates an error because the parameter must be a single value, not a range.
Second, my guess is: the root cause of the orginal error is that Match is returning an error.
You nave not provided sufficient information for us to comment further, without our making wild guesses that are likely to misguide you down a rabbit hole.
Either attach an example Excel file that demonstrates the problem, or at the very least, show us examples of the data and provide some useful information about the data, such as the type of the data. (Note: The type as indicated by ISTEXT or ISNUMBER, not the cell format.)
If you cannot attach a file (by clicking "browse" at the bottom of the reply window), then upload the file to a file-sharing website, and post the download URL. The website should not require that we log into it (except for onedrive.live.com, which I deprecate anyway). I prefer box.net/files; others like dropbox.com.
-----
Some wild-guess additional information....
If your data is "long strings of digits (10-16)", not only is type Long the wrong type because it is limited to integers with 10 or fewer digits (up to 2147483647), but also probably (hopefully) the data should be type String (text), not numeric.
Usually, VBA automatically converts strings to numbers if the context requires it. But perhaps the mix of types has something to do with the original problem -- especially, a difference in type (not format) between Cells(2,1) and the values in Range("E:E").
For debugging purposes, at least, I suggest that you break-up the Index/Match expression into multiple steps in order to isolate the root cause of the problem.
For example, single-step (by pressing f8) the following statements:
Dim a As Variant, b As Variant, c As Variant
a = Cells(2,1)
b = Application.Match(a, agingwb.Sheets("...").Range("E:E"))
c = Application.Index(agingwb.Sheets("...").Range("A:A"), b)
ws.Cells(27, 2).Value = c
At each step, use the Immediate Window (ctrl+g) to check the type and values of a, b and c etc.
-----
Also, I believe that you have an error in the order of statements to Set agingwb and wb.
And that might be the source of any Match or Index error.
With your order of statements, I believe that both agingwb and wb refer to the same workbook, namely the result of Workbooks.Open.
I believe the order should be:
Set wb = ActiveWorkbook
Set agingwb = Workbooks.Open(...)
I believe that ThisWorkbook.Activate is superfluous. But you probably want:
wb.Activate
The point is: I believe that Set ... = Workbooks.Open sets ActiveWorkbook to the newly opened workbook.
JoeUser2004 , i tried your method; there is no error when the code is executed but the index match is only populating the incorrect data; ie those accounts number that are inactive should be populated as inactive in the Lookup for acct status.xlsx file.
I have attached 2 workbooks; Lookup for acct status.xlsx and Corp_Aging.xlsx
Lookup for acct status.xlsx will be workbook that is containing the macro.
Vba code as follows:
Sub idAcc() Dim ws As Worksheet Dim agingwb As Workbook Dim lastRow As Long Dim i As Long Dim a As Variant Dim b As Variant Dim c As Variant Set ws = Sheet1 Set agingwb = Workbooks.Open("C:\Users\hrhquek\Desktop\Corp_Aging.xlsx") ThisWorkbook.Activate ws.Range("AA1").EntireColumn.Insert ws.Range("AA1").Value = "Asset_Status" lastRow = ws.Cells(ws.Rows.Count, "Z").End(xlUp).Row a = Cells(2, 1) b = Application.Match(a, agingwb.Sheets("details_open_amt_by_acc").Range("E:E")) c = Application.Index(agingwb.Sheets("details_open_amt_by_acc").Range("A:A"), b) For i = 2 To lastRow Range("AA" & i).Value = Application.WorksheetFunction.IfNa(c, "") Next i End Sub(Resubmitting response to correct bad previous suggestions.)
I'm afraid that I cannot help you further.
We are both making mistakes which have lead to a tangled mess.
I suggest that you "start over", sort of.
Attach Excel files that demonstrate the __original__ problem -- even before your CLng hack, which was misguided, as I explained previously.
(Previously, I suggested that you abandon this thread and start a new thread. That was a misdirection because it might bifurcate responses to this thread and the new thread.)
