如何使 match() 在 excel vba 中与日期一起使用？

How to make match() work with date in excel vba?

提问by Ting Ping

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 月 1 日，它仍然没有返回任何值。

Any idea how to fix it?

知道如何修复它吗？

采纳答案by glh

Your best bet is to use .Find(). This will return a rangeif found or nothingif not.

最好的办法是使用.Find(). 这将返回一个rangeif found 或nothingif not。

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

回答by user2140261

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

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

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/2008and 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().

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 可以是一个值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。不是日期，因此只需将现在日期值转换为数字即可使用CLng()

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

试一试它也会比使用 Find 替代方法快得多：

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：

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

回答by ivan_pozdeev

Bottom line:

底线：

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

• Alternatively, use a Datecell's Value2property(which will also be a Double) instead of Valuefor the search key.

• 用 WorksheetFunction.Match(CDbl(date), range, 0)

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

CLngsuggested in other answers would discard the time part of the date.

CLng在其他答案中建议将丢弃date.

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

Currency数据类型存在相同的问题，但您不能使用CDbl它（有关选项，请参见下文）。

---

---

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

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

• Dateis internally represented as IEEE 64-bit (8-byte) floating-point numbers where the integer part is the date and fractional part is the time
• Currencyis also 8-byte but is treated as a fixed-point number with 4 fractional digits (an integer scaled by 10'000)

• Date在内部表示为 IEEE 64 位（8 字节）浮点数，其中整数部分是日期，小数部分是时间
• Currency也是 8 字节，但被视为具有 4 个小数位的定点数（按 10'000 缩放的整数）

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

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

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

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

For the Currencytype, CDbldoes 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
• 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

• 如果您需要完全匹配，请以这种方式或其他方式使用密钥的精确表示（到 4 个小数位数），或者
• Round如果要比较的值来自其他地方和/或您只需要等于 2 个小数，则使范围内的单元格实际上是公式

回答by JML

This way it works using this method:

这样它就可以使用这种方法工作：

Nbr,L, Cas Integer

Nbr,L, C作为整数

Datedebas date

Datedeb作为日期

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

回答by Declan_K

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

回答by Gilles

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 = Format("YourDate","mmm-yyyy") YourResult = Application.match(Clng(Cdate(YourVar)), YourRange, 0)

Regards

问候

Gilles

吉尔斯