我正在嘗試添加一個鍵，然后將陣列日期（）資料型別中的日期添加到字典中，但是出現型別不匹配錯誤，有什么想法嗎？
Sub Test_Dates()
'
Dim TESTWB As Workbook
Dim TESTWS As Worksheet
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
Dim DatesDict As Scripting.Dictionary
For i = 1 To TESTWS.Cells(1, 1).End(xlDown).Row
DatesDict.Add TESTWS.Cells(i, 1), getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
Next i
End Sub
這是獲取 2 個日期 (i,2) 和 (i,3) 之間日期的函式
Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
Dim varDates() As Date
Dim lngDateCounter As Long
ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))
For lngDateCounter = LBound(varDates) To UBound(varDates)
varDates(lngDateCounter) = CDate(StartDate)
StartDate = CDate(CDbl(StartDate) 1)
Next lngDateCounter
getDates = varDates
ClearMemory:
If IsArray(varDates) Then Erase varDates
lngDateCounter = Empty
End Function
uj5u.com熱心網友回復：
似乎您的一個或多個行在 ColB 或 ColC 中沒有有效日期。
這將有助于除錯以避免隱式Date轉換：
例如：
Sub Test_Dates()
'
Dim TESTWB As Workbook
Dim TESTWS As Worksheet, i As Long, k
Dim DatesDict As Scripting.Dictionary
Dim dtStart As Date, dtEnd As Date
Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")
Set DatesDict = New Scripting.Dictionary
For i = 1 To TESTWS.Cells(Rows.Count, "A").End(xlUp).Row
With TESTWS.Rows(i)
dtStart = CDate(.Columns("B").Value) '<< explicit Date conversion
dtEnd = CDate(.Columns("C").Value)
If dtEnd >= dtStart Then
DatesDict.Add .Columns("A").Value, getDates(dtStart, dtEnd)
End If
End With
Next i
'checking...
For Each k In DatesDict
Debug.Print TypeName(k), k, DatesDict(k)(0)
Next k
End Sub
Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
Dim varDates() As Date
Dim lngDateCounter As Long
ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))
For lngDateCounter = LBound(varDates) To UBound(varDates)
varDates(lngDateCounter) = CDate(StartDate)
StartDate = CDate(CDbl(StartDate) 1)
Next lngDateCounter
getDates = varDates
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/440550.html