关于match函数为什么不能正常使用

wf888888

附件中，我查询其他文本内容均无问题，但查日期就查不出来，麻烦哪位大神告知原因，谢谢！

cidanji

日期转为"yyyy-m-d"格式文本查查

1245752336

match函数不用加worksheetfunction,直接使用
   sjh = Application.Match(CStr(Sheet1.Range("a" & i).Value), arr, 0) '查时间所在行
加CStr是强转成字符串，不然找不到。
另外还有一些想法。
一是不建议使用match函数，刚才运行了一下，速度很慢，建议使用字典提高效率。
二是没有错误判断，如果match函数没有找到，会报错，建议加上on error reusume next或者是application.iferror

wf888888

1245752336 发表于 2024-1-6 22:14
match函数不用加worksheetfunction,直接使用
   sjh = Application.Match(CStr(Sheet1.Range("a" & i).Val ...

谢谢，很中肯，我尝试一下

wf888888

wf888888 发表于 2024-1-6 22:56
谢谢，很中肯，我尝试一下

Sub test1()
Dim i, j As Long
Dim dic, dic1 As Object
Dim arr
Dim arr1
On Error Resume Next
Set dic = CreateObject("Scripting.Dictionary")
Set dic1 = CreateObject("Scripting.Dictionary")

arr = Sheet2.Range("A1").CurrentRegion
arr1 = Sheet2.Range("A1").CurrentRegion

For i = 2 To UBound(arr)
    dic.Add arr(i, 1), arr(i, 2)
Next i


For j = 2 To UBound(arr1)
    dic1.Add arr1(j, 1), arr1(j, 3)
Next j

arr = Sheet1.Range("A1").CurrentRegion
arr1 = Sheet1.Range("A1").CurrentRegion


For i = 2 To UBound(arr)
    arr(i, 2) = dic(arr(i, 1))
Next i


For j = 2 To UBound(arr1)
    arr1(j, 3) = dic1(arr1(j, 1))
Next j

Sheet1.Range("A1").CurrentRegion = arr
Sheet1.Range("A1").CurrentRegion = arr1

Set dic = Nothing
Set dic1 = Nothing

End Sub

程序
Sheet1.Range("A1").CurrentRegion = arr
Sheet1.Range("A1").CurrentRegion = arr1
上面两句以上部分运行均无问题，单独执行其中一句也无问题，合并运行存在问题，如何修改？

wf888888

1245752336 发表于 2024-1-6 22:14
match函数不用加worksheetfunction,直接使用
   sjh = Application.Match(CStr(Sheet1.Range("a" & i).Val ...

Sub test1()
Dim i, j As Long
Dim dic, dic1 As Object
Dim arr
Dim arr1
On Error Resume Next
Set dic = CreateObject("Scripting.Dictionary")
Set dic1 = CreateObject("Scripting.Dictionary")

arr = Sheet2.Range("A1").CurrentRegion
arr1 = Sheet2.Range("A1").CurrentRegion

For i = 2 To UBound(arr)
    dic.Add arr(i, 1), arr(i, 2)
Next i


For j = 2 To UBound(arr1)
    dic1.Add arr1(j, 1), arr1(j, 3)
Next j

arr = Sheet1.Range("A1").CurrentRegion
arr1 = Sheet1.Range("A1").CurrentRegion


For i = 2 To UBound(arr)
    arr(i, 2) = dic(arr(i, 1))
Next i


For j = 2 To UBound(arr1)
    arr1(j, 3) = dic1(arr1(j, 1))
Next j

Sheet1.Range("A1").CurrentRegion = arr
Sheet1.Range("A1").CurrentRegion = arr1

Set dic = Nothing
Set dic1 = Nothing

End Sub

程序
Sheet1.Range("A1").CurrentRegion = arr
Sheet1.Range("A1").CurrentRegion = arr1
上面两句以上部分运行均无问题，单独执行其中一句也无问题，合并运行存在问题，如何修改？