application.match type mismatch

[DevanG]

OK tried to get you all the info i could on this fishing through my code.... I am having problems with type mismatches in my application. match lines... I'm not so sure how these things work.. any help would be great.

[VBA] Dim shareClass, packageCode
Dim pkgCodeIndex As Integer, revenueIndex As Integer, nameIndex As Integer, SIAcodeindex As Integer, newFundIndex As Integer, i As Integer
Dim searchRange, resultRange, pkgCodeToRevenue, pkgCodeToName, quoteColumns

pkgCodeToRevenue = Array(2, 11, 20, 20, 29, 29, 38, 38, 38, 47)
pkgCodeToName = Array(23, 24, 25, 25, 26, 26, 27, 27, 27, 28)

If Workbooks(fs).Sheets("Quote").Cells(Fundname, 14).Value = "" Then
newFundIndex = Application.Match(Workbooks(fs).Sheets("Quote").Cells(13, 14).Value, Workbooks(fs).Sheets("Share Class info").Range("B13:K13"))
shareClass = Workbooks(fs).Sheets("Share Class info").Cells(Fundname, newFundIndex + 1).Value
Else
shareClass = Workbooks(fs).Sheets("Quote").Cells(Fundname, 14).Value ' Get share class name
End If

pkgCodeIndex = Application.Match(shareClass, searchRange, 0)
packageCode = Application.Index(resultRange, pkgCodeIndex)

' Using the package code index number, we map to a column in the revenue sheet
revenueIndex = pkgCodeToRevenue(pkgCodeIndex - 1)
nameIndex = pkgCodeToName(pkgCodeIndex - 1)
SIAcodeindex = pkgCodeToName(pkgCodeIndex - 1) + 6 + 7 * Sheets("Quote").Range("RPRev").Value[/VBA]

[Bob Phillips]

Are we supposed to be able to figure out the problem you are having just by looking at the code? More info is needed.

[DevanG]

Are we supposed to be able to figure out the problem you are having just by looking at the code? More info is needed.

Sorry -- There are type mismatches in the application.match lines like i said above... What else would you need to know?

Guess i'm asking people to check the data types and see why it would break at the
pkgCodeIndex = Application.Match(shareClass, searchRange, 0)

[Bob Phillips]

There is nothing in the code that I can see that loads SearchRange so that could cause an error, but maybe you load it elsewhere. We have no idea what is in ShareClass, whether that is relevant or not.

[DevanG]

There is nothing in the code that I can see that loads SearchRange so that could cause an error, but maybe you load it elsewhere. We have no idea what is in ShareClass, whether that is relevant or not.

Oh I'm sorry -- ShareClass will have a letter in it. The SearchRange is a range of cells.

[Kenneth Hobs]

The problem is that you set the return variable for the worksheetfunction Match to be integer when it is a variant. Some worksheetfunctions are funny that way.

e.g.
[vba]Sub test()
Dim pkgCodeIndex As Variant, shareClass As String, searchRange As Range
Range("A1") = "King"
Range("A2") = "Queen"
Range("A3") = "Peon"
Range("A4") = "Jester"
Set searchRange = Range("A1:A10")

shareClass = "Peonn"
pkgCodeIndex = Application.Match(shareClass, searchRange, 0)
MyMsg pkgCodeIndex, shareClass

shareClass = "Peon"
pkgCodeIndex = Application.Match(shareClass, searchRange, 0)
MyMsg pkgCodeIndex, shareClass
End Sub

Sub MyMsg(errDesc As Variant, sClass As String)
Dim msg As String
If CStr(errDesc) = "Error 2042" Then
msg = "Not Found"
Else: msg = "shareClass: " & sClass & vbLf & "Index: " & errDesc
End If
MsgBox msg
End Sub[/vba]

[DevanG]

OK then when I put that variant data into the variant array's criteria, why is that a mismatch as well? I tried turning the integers into variants before.

all of these variables are now variants.

revenueIndex = pkgCodeToRevenue(pkgCodeIndex - 1)

[Bob Phillips]

The problem is that you set the return variable for the worksheetfunction Match to be integer when it is a variant. Some worksheetfunctions are funny that way.

Uh? I always use Long and I have never had a problem that I recall.

[Kenneth Hobs]

That is odd xld. In my example, simply put Integer or Long as the Dim type for pkgCodeIndex. The reason you need Variant is because if not found, the function returns #N/A used as a standard formula whereas used in VBA, the return is "Error 2042".

DevanG, you have other issues going on. Where did you use the Set to set the searchRange? You should Dim searchRange as Range too. I guess that you could use an array but I don't even see where that is created for searchRange. Depending on the scenario, I have found that WorkSheetFunction.Transpose(somearray) or a double Transpose if needed to make it work in some worksheetfunctions in VBA.

When using specific sheets, range names, and such, it is easier for us to help if you make a simple example file and attach it. That way, we can better match to your naming convention and setups.

[DevanG]

That is odd xld. In my example, simply put Integer or Long as the Dim type for pkgCodeIndex. The reason you need Variant is because if not found, the function returns #N/A used as a standard formula whereas used in VBA, the return is "Error 2042".

DevanG, you have other issues going on. Where did you use the Set to set the searchRange? You should Dim searchRange as Range too. I guess that you could use an array but I don't even see where that is created for searchRange. Depending on the scenario, I have found that WorkSheetFunction.Transpose(somearray) or a double Transpose if needed to make it work in some worksheetfunctions in VBA.

When using specific sheets, range names, and such, it is easier for us to help if you make a simple example file and attach it. That way, we can better match to your naming convention and setups.

yea.. im not sure what's going on... searchRange is a variant and is set to a range.

[mikerickson]

If there is not Matching value (i.e. Application.Match returns #N/A), then there will be a type mismatch if the variable is declared other than Variant.

There will also be a type mismatch if a comparative (e.g. = < or >) trys to compare to an error value (even if it is dimmed Variant) with something else.

[Bob Phillips]

That is odd xld. In my example, simply put Integer or Long as the Dim type for pkgCodeIndex. The reason you need Variant is because if not found, the function returns #N/A used as a standard formula whereas used in VBA, the return is "Error 2042"..

Not really so odd Kenneth. My code actually caters for it, but in a different manner. I have a Match function that wraps the Application Match in a On Error, and so returns 0 if no match, I forgot I did it that way when I replied. I don't use variant, but I mange the error