请朋友们看看这段excel VBA code

Question

这是一段前任同事写的vba代码，然而最近却用不了。因为运行时错误‘13’，类型不匹配”，我不知道哪里出了问题。
是excel的数据格式出了问题还是什么原因。而且我看代码，column A 和 column B应该是日期是时间格式。但是我查看过，应该格式是对的。所以请问大神，这段代码应该是没问题的（运行过1个多月都很顺畅），那导致错误13 是什么原因呢？

Set wsCopy = wbcopy.Worksheets("Full List")
'Set wsDest = ThisWorkbook.Worksheets("CombineList")
yestercount = wsCopy.Cells(Rows.Count, 1).End(xlUp).Row
lCopyRow = 3
TmwCaseCount = 2
lDestRow = wsCopy.Cells(Rows.Count, 3).End(xlUp).Row
'Combined list last row
ComLstLast = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
For lCopyRow = 3 To ComLstLast
**'combine list date & time >= YTD & < TD 8:30 am -> add to Encls. Full list
If CLng(wsDest.Cells(lCopyRow, 1)) + wsDest.Cells(lCopyRow, 2) >= CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) - 1 And _
CLng(wsDest.Cells(lCopyRow, 1)) + wsDest.Cells(lCopyRow, 2) < CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) + 0.3541666666 Then
ListLastRow = ListLastRow +
wsDest.Activate
wsDest.Rows(lCopyRow).Copy
wsCopy.Activate
wsCopy.Rows(ListLastRow).Select
wsCopy.Rows(ListLastRow).Insert
Else
'combine list date & time > TD 8:30 am -> add to Encls. Full list (bottom rows for tmr, red color)
If CLng(wsDest.Cells(lCopyRow, 1)) + wsDest.Cells(lCopyRow, 2) > CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) + 0.3534722222 Then '416667 Then
TmwCaseCount = TmwCaseCount + 1
wsDest.Activate
wsDest.Rows(lCopyRow).Copy
wsCopy.Activate
wsCopy.Rows(ListLastRow + TmwCaseCount).Select
wsCopy.Rows(ListLastRow + TmwCaseCount).Insert
wsCopy.Rows(ListLastRow + TmwCaseCount).Interior.ColorIndex = 3
Else
'combine list date & time < YTD & >= TD - 2 -> add to YTD's last row, green color
If CLng(wsDest.Cells(lCopyRow, 1)) + wsDest.Cells(lCopyRow, 2) < CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) - 1 Then
If CLng(wsDest.Cells(lCopyRow, 1)) + wsDest.Cells(lCopyRow, 2) >= CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) - 2 Then
yestercount = yestercount + 1
ListLastRow = ListLastRow + 1
wsDest.Activate
wsDest.Rows(lCopyRow).Copy
wsCopy.Activate
wsCopy.Rows(yestercount).Select
wsCopy.Rows(yestercount).Insert
wsCopy.Range(wsCopy.Cells(yestercount - 1, 3), wsCopy.Cells(yestercount - 1, 3)).AutoFill wsCopy.Range(wsCopy.Cells(yestercount - 1, 3), wsCopy.Cells(yestercount, 3)), xlFillSeries
wsCopy.Rows(yestercount).Interior.Color = RGB(169, 208, 142)
OldCaseCount = lDestRow
'< TD - 2
Else
'For OldCaseCount = lDestRow - 4500 To lDestRow
For OldCaseCount = Application.Max(3, lDestRow - 4500) To lDestRow
'case no. e.g. 20210315/052
'Left 8 char of case no. = yyyymmdd
'if case no. left 8 char in Full list = date value in combine list -> add to Full list Old case rows
If Left(wsCopy.Cells(OldCaseCount, 3), 8) = CStr(Format(wsDest.Cells(lCopyRow, 1) + 1, "yyyymmdd")) Then
ListLastRow = ListLastRow + 1
yestercount = yestercount + 1
wsDest.Activate
wsDest.Rows(lCopyRow).Copy
wsCopy.Activate
wsCopy.Rows(OldCaseCount).Select
wsCopy.Rows(OldCaseCount).Insert
wsCopy.Range(wsCopy.Cells(OldCaseCount - 1, 3), wsCopy.Cells(OldCaseCount - 1, 3)).AutoFill wsCopy.Range(wsCopy.Cells(OldCaseCount - 1, 3), wsCopy.Cells(OldCaseCount, 3)), xlFillSeries
wsCopy.Rows(OldCaseCount).Interior.Color = RGB(169, 208, 142)
OldCaseCount = lDestRow
End If
Next
End If
End If
End If
End If
Next

lDestRow = yestercount + 1
wsCopy.Cells(lDestRow, 3) = Format(ThisWorkbook.Worksheets("Control").Cells(2, 4).Value - 1, "yyyymmdd") + "/001"

Set casenorng = wsCopy.Range(wsCopy.Cells(lDestRow, 3), wsCopy.Cells(ListLastRow, 3))
wsCopy.Range(wsCopy.Cells(lDestRow, 3), wsCopy.Cells(lDestRow, 3)).AutoFill casenorng, xlFillSeries

ThisWorkbook.Worksheets("Table 7").Cells(12, 2).Value = wbcopy.Name

Set wbcopy = Nothing: Set wsCopy = Nothing: Set wsDest = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

Private Sub ResetList_Click()
'reset the list'

'ThisWorkbook.Worksheets("CombineList").Range("A3:AK1000").Interior.Color = xlNone
'ThisWorkbook.Worksheets("CombineList").Range("A3:AK1000").Font.Color = xlNone

'ThisWorkbook.Worksheets("CombineList").Range("A3:AK1000").ClearContents
'Newly added
ThisWorkbook.Worksheets("CombineList").Range("A3:BC1000").ClearContents
'MsgBox (CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) + ThisWorkbook.Worksheets("Control").Cells(2, 7) & " " & CLng(ThisWorkbook.Worksheets("Control").Cells(2, 4)) + 0.35416667)
End Sub

Answer 1

根据你提供的代码，错误13是类型不匹配的。具体来说，这个错误通常发生在尝试将一个不兼容的数据类型赋值给一个变量或表达式时。在你的代码中，可能有以下几个地方导致了这个错误：

1. 数据类型不匹配：请确保你的日期和时间数据在列A和列B中都是正确的日期和时间格式。如果数据格式正确，那么可能是其他地方的数据类型不匹配导致了错误。

2. 数据范围超出限制：请确保你的数据范围在合理的范围内，不超过VBA所支持的数据类型的范围。

3. 其他变量或表达式的数据类型不匹配：请检查代码中其他地方使用到的变量或表达式，确保它们的数据类型与所需的数据类型匹配。

为了更准确地找出问题所在，你可以尝试使用调试工具来逐步执行代码并观察每一步的结果。你可以使用VBA的调试工具，如设置断点、单步执行等方法来帮助你找到问题所在。