英文:
Match to find a date in a list yields different answers depending on the formatting of the dates
问题
以下是代码部分的翻译：
Sub findDate()
Dim dateToFind
Dim arr()
Dim Rng As Range
dateToFind = Cells(275, "ag").Value
Set Rng = Worksheets("calcSheet").Range("A1:A6216")
arr = Rng
a = WorksheetFunction.Match(dateToFind, arr, 1)
End Sub
在处理短日期格式时，如果您想要获得正确的答案，您可以尝试以下更改：
Sub findDate()
Dim dateToFind As Date
Dim arr() As Variant
Dim Rng As Range
dateToFind = CDate(Cells(275, "AG").Value) ' 使用CDate将单元格的值转换为日期类型
Set Rng = Worksheets("calcSheet").Range("A1:A6216")
arr = Rng.Value ' 使用Value属性获取范围的值
a = WorksheetFunction.Match(dateToFind, arr, 0) ' 使用0来进行精确匹配
End Sub
这个更改将确保日期以日期数据类型进行比较，同时使用0来进行精确匹配，以解决短日期格式引起的问题。
英文:
I have a list of dates (work days, no weekends) on column A that is sorted in ascending order. The column has a string header that reads "Dates." The format of the column is m/d/yyyy.
The first date is 5/28/1998 on cell A2.
The last date is 2/9/2023 on cell A6216.
I have a date 6/28/1999 on cell AG275. I need to find the row for that date on column A.
This code finds the date, as long as both column A and cell AG275 are formatted as General.
In that case, cell AG275 reads as 36337 and the answer (variable a in the code below) returns 275, which is the row where 36337 is.
Sub findDate()
Dim dateToFind
Dim arr()
Dim Rng As Range
dateToFind = Cells(275, "ag").Value
Set Rng = Worksheets("calcSheet").Range("A1:A6216")
arr = Rng
a = WorksheetFunction.Match(dateToFind, arr, 1)
End Sub
The problem is that if both column A and cell AG275 are formatted as short date then the code answer (variable a) is not 275 but 1530, which is 6/25/2004, which in General formatting is 38163 (the following date on cell A1531 is 6/28/2004).
I could add code to reformat everything before starting the code and work with numbers instead of dates, but I'd rather work with the given dates.
How can I get the answer if everything is formatted as short dates?
I suspect the problem may have to do with the way I am declaring the variables and the array. I tried declaring as Date, as Variant, as Long and as Integer.
I also tried, as I read in related posts,
dateToFind = CDate(Cells(275, "ag").Value)
and
dateToFind = CLng(CDate(Cells(275, "ag").Value))
The behavior is the same for long dates (mm/dd/yyyy).
答案1
得分: 1
以下是翻译好的部分：
"不管单元格的格式如何，如果单元格的内容是日期，要查找的单元格内容也是日期，以下的代码将起作用："
Sub findDate()
Dim dateToFind As Variant
Dim Rng As Range
dateToFind = Cells(275, "ag")
Set Rng = Worksheets("calcSheet").Range("A1:A6216")
a = Application.Match(CLng(dateToFind), Rng, 1)
End Sub
英文:
No mater the formatting if the content of the cells are dates and the content of the cell to find is a date, the following will work:
Sub findDate()
Dim dateToFind As Variant
Dim Rng As Range
dateToFind = Cells(275, "ag")
Set Rng = Worksheets("calcSheet").Range("A1:A6216")
a = Application.Match(CLng(dateToFind), Rng, 1)
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论