excel - .FIND 找不到日期

Question

vartype(Date_var) 为 8，即 Vbstring。Newsheet.Range("B5") 的 vbtype 为 7，即 vbdate，值如下。Date_var = 20211207,Newsheet.Range("B5") = 07/12/2021 并在 numberformat 之后将其转换为 20211207。尽管存在日期，但 .Find 无法获取列号。我尝试将 date_var 转换为 Cdate 并且我得到类型不匹配。任何帮助深表感谢。

    Date_var = Worksheets("sheet1").Range("AF1:AL1000").Cells(1, 4).Value
    
    If Date_var = "" And Lastrow = 1 Then
       Exit Sub
    End If
    
    With Newsheet.Range("B5:BL5")
       .NumberFormat = "yyyymmdd"
        Set Columnfind = .Find(What:=Date_var, LookIn:=xlValues, lookat:=xlPart, 
        MatchCase:=False, SearchFormat:=False)
        If Columnfind Is Nothing Then
           colnum = 0
        Else
           colnum = Columnfind.Column
      End If
     
    End With

标签： excelvba

Answer 1

您可以检查您正在查找的数据的值和类型以及搜索区域中的数据，然后根据需要转换您正在查找的数据。在第一个选项中，搜索不同类型的数据，并且Date（考虑语言环境）未找到。在第二个变体Date中转换为String（考虑语言环境）并且搜索成功。

选项1

Sub test1()
    Date_vars_array = Array(20211207#, 20211207, "20211207", #12/7/2021#)   ' or #7/12/2021# for your locale
    
    For Each Date_var In Date_vars_array
        With NewSheet.Range("B5:BL5")
            .NumberFormat = "yyyymmdd"
            Set Columnfind = .Find(What:=Date_var, LookIn:=xlValues, lookat:=xlWhole, _
                MatchCase:=False, SearchFormat:=False)
            If Columnfind Is Nothing Then
               colnum = 0
            Else
                 colnum = Columnfind.Column
            End If
        End With
        Debug.Print ".Finding for 'Date_var = " & Date_var & "' which has '" & TypeName(Date_var) & "' data type:"
        Debug.Print vbTab & "'NewSheet.Range(""B5"") = " & NewSheet.Range("B5").Value & "' and has '" & TypeName(NewSheet.Range("B5").Value) & "' data type"
        Debug.Print vbTab & "'colnum = " & colnum & "'" & vbLf
    Next
End Sub

输出 1

.Finding for 'Date_var = 20211207' which has 'Double' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 20211207' which has 'Long' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 20211207' which has 'String' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 07.12.2021' which has 'Date' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 0'

选项 2（如果 TypeName(Date_var) = "Date" Then Date_var = Format(Date_var, "yyyymmdd") 添加）

Sub test1()
    Date_vars_array = Array(20211207#, 20211207, "20211207", #12/7/2021#)   ' or #7/12/2021# for your locale
    
    For Each Date_var In Date_vars_array
        With NewSheet.Range("B5:BL5")
            .NumberFormat = "yyyymmdd"
            
            If TypeName(Date_var) = "Date" Then Date_var = Format(Date_var, "yyyymmdd")
            
            Set Columnfind = .Find(What:=Date_var, LookIn:=xlValues, lookat:=xlWhole, _
                MatchCase:=False, SearchFormat:=False)
            If Columnfind Is Nothing Then
               colnum = 0
            Else
                 colnum = Columnfind.Column
            End If
        End With
        Debug.Print ".Finding for 'Date_var = " & Date_var & "' which has '" & TypeName(Date_var) & "' data type:"
        Debug.Print vbTab & "'NewSheet.Range(""B5"") = " & NewSheet.Range("B5").Value & "' and has '" & TypeName(NewSheet.Range("B5").Value) & "' data type"
        Debug.Print vbTab & "'colnum = " & colnum & "'" & vbLf
    Next
End Sub

输出 2

.Finding for 'Date_var = 20211207' which has 'Double' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 20211207' which has 'Long' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 20211207' which has 'String' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

.Finding for 'Date_var = 20211207' which has 'String' data type:
    'NewSheet.Range("B5") = 07.12.2021' and has 'Date' data type
    'colnum = 2'

旁注：查找搜索文本