Paul_Hossler
06-02-2012, 08:40 AM
I'm trying to come up with a more flexible Match () function which isn't so dependent on compatible data types
For example, if the data being searched contains a numeric 123, but the data to be Match-ed is string "123", it returns N/A
The best I've been able to come up with MyMatch() below (proof of concept only = not very polished or robust).
It just feels 'crude' and having to do 4 Matchs each time could start adding up to a performance hit. Once I get this sorted out, VLookup is next
Is there any better way?
Option Explicit
Sub test1()
Dim v As Variant
Range("A1").Value = "aaa"
Range("A2").Value = "bbb"
Range("A3").Value = #1/1/2012#
Range("A4").Value = 123
Range("A5").Value = "ddd"
Range("A6").Value = "'456"
v = "bbb"
MsgBox MyMatch(v, Range("A:A"))
v = 123
MsgBox MyMatch(v, Range("A:A"))
v = "123"
MsgBox MyMatch(v, Range("A:A"))
v = 456
MsgBox MyMatch(v, Range("A:A"))
v = "456"
MsgBox MyMatch(v, Range("A:A"))
v = #1/1/2012#
MsgBox MyMatch(v, Range("A:A"))
End Sub
Function MyMatch(v As Variant, r As Range) As Long
Dim N As Long
N = 0
On Error Resume Next
'case 1 -- 123 finds 123, and "abc" finds "abc"
N = Application.WorksheetFunction.Match(v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 2 -- "123" finds 123
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(--v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 3 -- 123 finds "123"
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(CStr(v), r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 4 -- 1/1/2012 finds 1/1/2012
If IsDate(v) Then N = Application.WorksheetFunction.Match(CLng(v), r.Columns(1), 0)
NiceExit:
On Error GoTo 0
MyMatch = N
End Function
Paul
For example, if the data being searched contains a numeric 123, but the data to be Match-ed is string "123", it returns N/A
The best I've been able to come up with MyMatch() below (proof of concept only = not very polished or robust).
It just feels 'crude' and having to do 4 Matchs each time could start adding up to a performance hit. Once I get this sorted out, VLookup is next
Is there any better way?
Option Explicit
Sub test1()
Dim v As Variant
Range("A1").Value = "aaa"
Range("A2").Value = "bbb"
Range("A3").Value = #1/1/2012#
Range("A4").Value = 123
Range("A5").Value = "ddd"
Range("A6").Value = "'456"
v = "bbb"
MsgBox MyMatch(v, Range("A:A"))
v = 123
MsgBox MyMatch(v, Range("A:A"))
v = "123"
MsgBox MyMatch(v, Range("A:A"))
v = 456
MsgBox MyMatch(v, Range("A:A"))
v = "456"
MsgBox MyMatch(v, Range("A:A"))
v = #1/1/2012#
MsgBox MyMatch(v, Range("A:A"))
End Sub
Function MyMatch(v As Variant, r As Range) As Long
Dim N As Long
N = 0
On Error Resume Next
'case 1 -- 123 finds 123, and "abc" finds "abc"
N = Application.WorksheetFunction.Match(v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 2 -- "123" finds 123
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(--v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 3 -- 123 finds "123"
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(CStr(v), r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 4 -- 1/1/2012 finds 1/1/2012
If IsDate(v) Then N = Application.WorksheetFunction.Match(CLng(v), r.Columns(1), 0)
NiceExit:
On Error GoTo 0
MyMatch = N
End Function
Paul