如何使用日期引用查找列

Question

我正在处理表，因此它们都是ListObjects中的引用。我需要通过使用另一个表中的范围来知道ListObjects.HeaderRowRange中日期的列是什么。我尝试了find和match方法。如以下示例所示：

Sub test()
Dim mycell As Range
Dim myrange As Range
Dim SourceTbl As ListObject
Dim ResultTbl As ListObject
Set SourceTbl = WSEscoreCocho.ListObjects("TBEscore")
Set ResultTbl = WSBD.ListObjects("TBBD")
Set myrange = ResultTbl.ListColumns(5).DataBodyRange
For Each mycell In myrange
mycell.Value = SourceTbl.HeaderRowRange.Find(mycell.Offset(0, -1).Value, LookAt:=xlWhole).Column
mycell.Value = WorksheetFunction.Match(mycell.Offset(0, -1).Value, SourceTbl.HeaderRowRange, 0)
Next mycell
End Sub

但不起作用。此代码返回"尚未定义对象变量"。问题似乎出在数据的存储上。我的第一个mycell.offset(0,-1).value是2020年3月9日。没有引号。我试过Clng()和Cdate。我该如何解决这个问题？

Answer 1

只需将mycell.offset(0,-1).value声明为string就可以解决此问题。代码是：

Sub test()
Dim mycell As Range
Dim myrange As Range
Dim SourceTbl As ListObject
Dim ResultTbl As ListObject
Dim mydate as String
Set SourceTbl = WSEscoreCocho.ListObjects("TBEscore")
Set ResultTbl = WSBD.ListObjects("TBBD")
Set myrange = ResultTbl.ListColumns(5).DataBodyRange
For Each mycell In myrange
mydate = DateSerial(Year(mycell.Offset(0, -1).Value), Month(mycell.Offset(0, -1).Value), Day(mycell.Offset(0, -1).Value)) 
mycell.Value = SourceTbl.HeaderRowRange.Find(mydate, LookAt:=xlWhole).Column
Next mycell
End Sub