如何使match（）在excel vba中使用日期？

I'm having problem making the match() work in excel VBA. The code is:

我有问题使match（）在excel VBA中工作。代码是：

x = Application.Match("Sep 2008", Range("F1:F1"), 0)

The value in cell F1 is 9/1/2008.

单元格F1中的值是9/1/2008。

Even if I changed Sep 2008 to 9/1/2008, it still doesn't return any value.

即使我将2008年9月更改为2008年9月9日，它仍然没有返回任何值。

Any idea how to fix it?

知道怎么解决吗？

6 个解决方案

#1

Your best bet is to use .Find(). This will return a range if found or nothing if not.

你最好的选择是使用.Find（）。如果找到则返回范围，否则返回任何值。

Set x = Range("F1:F1").Find(CDate("Sept 2008"), , , xlWhole)

If you wanted the column number:

如果您想要列号：

x = Range("F1:F1").Find(CDate("Sept 2008"), , , xlWhole).Column

With capture of not found

捕获未找到

Sub test()

    Dim y As Date, x As Variant, c As Long
    y = CDate("Sep 2008")
    Set x = Range("1:1").Find(y, , , xlWhole)
    If Not x Is Nothing Then
        c = x.Column '<~~found
    Else
        Exit Sub 'not found
    End If

End Sub

#2

The reason why Even if I changed Sep 2008 to 9/1/2008, it still doesn't return any value.

之所以即使我将2008年9月更改为2008年9月9日，它仍然没有返回任何值。

Is because when there is a Date in excel, Excel automatically converts that date to a numeric value, What you really want to search for is:

是因为当excel中有一个Date时，Excel会自动将该日期转换为数值，您真正想要搜索的是：

39692

39692

This number is the number of days between 9/1/2008 and excel default of 1/1/1900

此数字是9/1/2008和excel默认值1/1/1900之间的天数

every date in excel is stored with a value like this. So the easiest way to handle this would be to convert what you see as a date to what excel sees as a date using CDate().

excel中的每个日期都存储有这样的值。因此，处理此问题的最简单方法是使用CDate（）将您看到的日期转换为excel所看到的日期。

This by itself will give you an unuseful error that vba can't get the property.

这本身就会给你一个无用的错误，即vba无法获得该属性。

That is because the Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. Not a date so simply convert the now date value to a number to search for the matching number in the list using CLng()

这是因为Lookup_value可以是值（数字，文本或逻辑值）或对数字，文本或逻辑值的单元格引用。不是日期，所以只需将now date值转换为数字，即可使用CLng（）在列表中搜索匹配的数字

Give this a shot it will also be much faster then using the Find alternative:

给它一个镜头它也会比使用查找替代方法快得多：

x = WorksheetFunction.Match(CLng(CDate("Sep 2008")), Range("F1:F1"), 0)

This should give you the result expected

这应该给你预期的结果

To handle when no match is found try this Sub:

要在没有找到匹配项时进行处理，请尝试以下

Sub MatchDate()
Dim myvalue As Double
Dim LastRow As Long

LastRow = Cells(Rows.Count, "F").End(xlUp)

On Error GoTo NotFound
myvalue = WorksheetFunction.Match(CLng(CDate("Sep 2008")), Range("F1:F" & LastRow), 0)
MsgBox (myvalue)
End

NotFound:
MsgBox ("No Match Was Found")
End

End:
End Sub

#3

This way it works using this method:

这种方式使用此方法：

Nbr,L, C as Integer

Nbr，L，C为整数

Datedeb as date

Datedeb作为日期

nbr = WorksheetFunction.Match(CLng(CDate(Datedeb)), Range(Cells(L, C), Cells(L + 100, C)), 0)

#4

Bottom line:

底线：

use WorksheetFunction.Match(CDbl(date), range, 0)

使用WorksheetFunction.Match（CDbl（日期），范围，0）
Alternatively, use a Date cell's Value2 property (which will also be a Double) instead of Value for the search key.

或者，使用Date单元格的Value2属性（也将是Double），而不是搜索键的Value。

CLng suggested in other answers would discard the time part of the date.

其他答案中建议的CLng将丢弃该日期的时间部分。

The same problem exists for the Currency data type but you can't use CDbl for it (see below for options).

货币数据类型存在同样的问题，但您不能使用CDbl（请参阅下面的选项）。

Range.Value2 Property (Excel) article suggests that Date and Currency types are "special" in that they have an "internal representation" that's in stark contrast with displayed value. Indeed:

Range.Value2 Property（Excel）文章建议Date和Currency类型是“特殊的”，因为它们具有与显示值形成鲜明对比的“内部表示”。确实：

Date is internally represented as IEEE 64-bit (8-byte) floating-point numbers where the integer part is the date and fractional part is the time
日期在内部表示为IEEE 64位（8字节）浮点数，其中整数部分是日期，小数部分是时间
Currency is also 8-byte but is treated as a fixed-point number with 4 fractional digits (an integer scaled by 10'000)
货币也是8字节，但被视为具有4个小数位的定点数（一个整数除以10'000）

Apparently, Match compares these internal values for performance reasons. So, we must ensure that they, rather than the readable representations, match exactly.

显然，Match会出于性能原因比较这些内部值。因此，我们必须确保它们而不是可读表示完全匹配。

Since Date is already floating-point internally, CDbl(date) doesn't actually change the data.

由于Date内部已经是浮点数，因此CDbl（date）实际上不会更改数据。

For the Currency type, CDbl does change data, so it's out of question. So either

对于Currency类型，CDbl确实会更改数据，因此它是不可能的。所以要么

use the exact representation of the key (to 4 fractional digits) this way or another if you require exact match, or
如果您需要完全匹配，则以这种方式使用密钥的精确表示（至4个小数位），或者
make the cells in the range actually be formulas with Round) if the value to compare with comes from elsewhere and/or you only require equality to 2 fractional digits
如果要比较的值来自其他地方和/或您只需要等于2个小数位，则使范围内的单元格实际上是圆形公式

#5

I think I can safely assume that the value in F1 is a date. In you code "Sep 2008" is a string. You will never be able to get a successful match as long as your datatypes are inconsistent. If you are looking for a date, then make sure that the first parameter is a date.

我想我可以放心地假设F1中的值是一个日期。你的代码“Sep 2008”是一个字符串。只要数据类型不一致，您就永远无法获得成功的匹配。如果您要查找日期，请确保第一个参数是日期。

Dim dSearchSDate As Date

dSearchSDate = "01/Sept/2008"

x = Application.Match(dSearchSDate, Range("F1:F1"), 0)

Here is another possible approach.

这是另一种可能的方法。

Sub temp()
Dim x
Dim dSearchSDate As Date

    dSearchSDate = "01/Sept/2008"

    If ThisWorkbook.Worksheets(1).Range("F1:F1").Value = dSearchSDate Then
        Debug.Print "Found it!"
    Else
        Debug.Print "Doh!!"
    End If

End Sub

#6

I know this post is old, but I had the same issue, and did find the answer.

我知道这篇文章很老，但我遇到了同样的问题，并找到了答案。

To make it work, you first need to make VBA see the same data formatting as it appears in your excel spreadsheet :

要使其正常工作，首先需要让VBA看到与excel电子表格中显示的数据格式相同的数据格式：

YourVar = Format("YourDate","mmm-yyyy") YourResult = Application.match(Clng(Cdate(YourVar)), YourRange, 0)

YourVar =格式（“YourDate”，“mmm-yyyy”）YourResult = Application.match（Clng（Cdate（YourVar）），YourRange，0）

Regards

问候

Gilles

吉尔斯

#1