VBA.查找，日期，序列号日期和数据类型属性(.Text，.Value，.Value2) [英] VBA .Find, dates, serial number dates, and the data type properties (.Text, .Value, .Value2)

问题描述

使用Range.Find方法遍历一个范围中的每个值以查看另一个值中是否存在.问题是两个范围的格式都不同(但所有值表面上都是日期)，这导致许多假否定"(即，值本应匹配时却不匹配).是否可以控制输入范围和搜索范围的数据类型，以便系统将苹果之间进行比较并成功识别匹配项?

Using the Range.Find Method to iterate through each value in one range to see if it exists in the other. Issue is that the format for both ranges is different (but all the values are ostensibly dates), which results in many "false negatives" (i.e., values are not matching when they should be). is it possible to control the data type for both the input and search ranges so that the system will compare apples-to-apples and successfully recognize matches?

以下是数据和代码:

Sheet1 (custom format, "yyyy-mm-dd")  
A1  2016-01-01  
A2  2016-01-02  
A3  2016-01-03  
A4  2016-01-04  
A5  2016-01-05  

Sheet2 (text format)  
A1  2016-01-01  
A2  2016-01-03  
A3  2016-01-05  

Sheet3 (display as "yyyy-mm-dd")  
[NO DATA]  

Sheet4 (display as "yyyy-mm-dd")   
[NO DATA]  

代码:

 Sub FindTest()  
            Dim inputRange As Range  
            Dim searchRange As Range  
            Dim found As Range  
            Set inputRange = Worksheets(1).Cells(1, 1).Resize(7, 1)  
            Set searchRange = Worksheets(2).Cells(1, 1).Resize(5, 1)  
            For Each i In inputRange  
                Set found = searchRange.Find _  
                    (What:=i, _  
                    after:=Cells(1, 1), _  
                    LookIn:=xlValues, _  
                    LookAt:=xlWhole, _  
                    SearchOrder:=xlByRows, _  
                    SearchDirection:=xlNext, _  
                    MatchCase:=False, _  
                    SearchFormat:=False)  
                If Not (found Is Nothing) Then  
                    Worksheets(3).Cells(i.Row, i.Column) = i  
                Else  
                    Worksheets(4).Cells(i.Row, i.Column) = i  
                End If  
            Next i  
        End Sub  

为此，我假设我需要将inputRange和searchRange中的所有值都与.Text进行比较，但我不确定是否到达那里.

For this to work, I assume that I need all the value in inputRange and searchRange to be compared as .Text, but I'm not sure to get there.

推荐答案

如果您需要将数据保留为真实日期和类似日期的文本，请在运行时格式化真实日期值以查找匹配项在文本日期"中.

If you need to keep your data as true dates and text-that-looks-like-dates then format the true date values on the he fly to locate matches within the 'text-dates'.

Sub FindTest()
    Dim inputRange As Range, i As Range
    Dim searchRange As Range, found As Range

    Set inputRange = Worksheets(1).Cells(2, 1).Resize(99, 1)
    Set searchRange = Worksheets(2).Columns(1)

    For Each i In inputRange
        If IsDate(i) Then                      ' ▼ format into TXT  here ▼
            Set found = searchRange.Find(What:=Format(i.Value2, "yyyy-mm-dd"), _
                            after:=Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False)
            If Not (found Is Nothing) Then
                With Worksheets(3)
                    .Cells(i.Row, i.Column) = i.Address(external:=True)
                    .Cells(i.Row, i.Column + 1) = i.Address(external:=True)
                End With
            Else
                With Worksheets(4)
                    .Cells(i.Row, i.Column) = i.Address(external:=True)
                    .Cells(i.Row, i.Column + 1) = i.Address(external:=True)
                End With
            End If
        End If
    Next i
End Sub

或者， Range.Text属性应该提供相同的答案.应用Trim命令以确保inputRange日期未在格式掩码中以尾随 _)格式化.

Alternately, the Range.Text property should provide the same answer. Apply the Trim command to make sure that the inputRange dates are not formatted with a trailing _) in the format mask.

        Set found = searchRange.Find(What:=Trim(i.Text), _
                        after:=Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)

附录:

如果 searchRange 中可能存在前导或尾随空格，则将搜索参数更改为 LookAt:= xlPart .尽管预先修复数据仍然是更好的选择，但它将忽略可能干扰成功匹配的无关字符.

If there is a possibility of leading or trailing spaces in the searchRange, then change the parameters of your search to LookAt:=xlPart. While repairing the data beforehand would still be the better option, it will ignore extraneous characters that could interfere with a successful match.

如果可以选择修复数据，则可以使用范围.具有YMD的文本值上的TextToColumns方法

If repairing the data can be considered an option then a blanket Range.TextToColumns method on the text values with a YMD xlColumnDataType will quickly convert your yyyy-mm-dd 'text-dates' into real dates.

    With searchRange
        .TextToColumns Destination: .Cells (1), DataType:=xlFixedWidth, _
                       FieldInfo:=Array(0, 5)
    End With

这将删除前导/后缀多余的字符，并为您留下一列真实的日期.

This will remove leading/trailing extraneous characters and leave you with a column of true dates.

这篇关于VBA.查找，日期，序列号日期和数据类型属性(.Text，.Value，.Value2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！