Application.Match error

[KasperC]

Hello,

I'm still struggeling to figure out the match function and how it works. Sometimes it works just fine and does what I want, other times it just spits out errors without me understanding why.

I'm puzzeled as of why this results in an error:
(Run-time error '13': Type mismatch)

Sub test()
    Dim ws As Worksheet
    Dim wso As Worksheet
    Dim wb As Workbook
    Dim r As Range
    Dim a
    Dim i As Long, LRow As Long, LRowo As Long
    Dim Vrnr As String

    
    Set wb = Excel.Workbooks("myworkbook.xlsm")
    Set ws = wb.Worksheets("Sheet1")
    Set wso = wb.Worksheets("Sheet2")
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    LRowo = wso.Cells(Rows.Count, 1).End(xlUp).Row
    
    a = ws.Range(ws.Cells(1, 1), ws.Cells(LRow, 1)).Resize(, 7)
    
    Vrnr = ws.Cells(2, 1).Value                                                'The value of this Cell is "81085", I've also tried without the ".Value"
    Set r = wso.Range(wso.Cells(2, 1), wso.Cells(20, 1))         'This range contains the value "81085", twice. Both in number formats
    
    Bestnr = Application.Match(Vrnr, r, 0)                              'This is where the error occurs.

End Sub

Does anyone have any ideas?

Thank you for your time.

Best regards,
Kasper C

 

[KasperC]

Update:
When i change the "Vrnr" to a Double instead of a String it seems to work just fine.
But why does it not work here, while working just fine in other workbooks dealing with the same data-sets..?

 

[StephenCrump]

You've almost answered your own question ....

If you're comparing strings "81085" and numerics 81085, than an exact MATCH will return an error (#N/A in Excel). Which is exactly what you're doing if you declare Vrnr as a String, and try to match it with numerics.

You haven't declared Bestnr in Sub test(). By default, this would be a Variant data type, which can accommodate error values. If Bestnr was a Variant and the MATCH failed, then it would have the value Error 2042. The fact that you're getting a Run-time error '13': Type mismatch instead suggests that you have declared Bestnr, probably as a Long (?) outside the Sub.

But why does it not work here, while working just fine in other workbooks dealing with the same data-sets..?

If MATCH is working elsewhere, then presumably you're comparing like with like, i.e. numeric values with numeric, or text with text?

 

[KasperC]

You've almost answered your own question ....

If you're comparing strings "81085" and numerics 81085, than an exact MATCH will return an error (#N/A in Excel). Which is exactly what you're doing if you declare Vrnr as a String, and try to match it with numerics.

You haven't declared Bestnr in Sub test(). By default, this would be a Variant data type, which can accommodate error values. If Bestnr was a Variant and the MATCH failed, then it would have the value Error 2042. The fact that you're getting a Run-time error '13': Type mismatch instead suggests that you have declared Bestnr, probably as a Long (?) outside the Sub.



If MATCH is working elsewhere, then presumably you're comparing like with like, i.e. numeric values with numeric, or text with text?

I Suppose it must be a decleration issiue - as you're mentioning it I can see that the variable isnt declared in some of my project - that must be the issiue.

Thank you so much for pointing that (somewhat obvious) fact out, helps alot!

If you don't mind me asking, what are the "best" data types to use when working with match functions?
As of right not Double for numbers and String seems to work, but string also seem to fail sometimes.
Say i want to match "123 - 456" in a data-set build up of a 3 number " - " 3 number combo - what data-type would you declare it as?

 

[MARK858]

If the cell literally contains 123 - 456 it will be text not a real number, therefore a String