vba - 错误类型 13,不匹配,一半时间用于 If 语句
问题描述
我正在使用 VBA 自动生成报告,除了我的一个If
语句之外,代码运行得相对较好。事实上,它每隔一次工作一次,当它不工作时,我会收到不匹配错误。在整个早上尝试解决这个问题后,我认为我正在使用的变量类型可能存在问题,或者我在某些时候使用了文本格式。
这是我遇到问题的部分,错误,当它弹出时,在ElseIf
下面标记的语句中这样做
Dim source As Excel.Workbook 'ARS15.xls
Dim target As Excel.Workbook 'reporting cc
Dim clist As Excel.Workbook 'client list
Dim i As Integer
Dim lastRow As Long
Dim rowz As Long
Dim temp As Range 'payment condition and client codes used for index match
Dim pay As Variant 'payment condition
Dim fact As Variant 'insurance status
'get payment conditions and insurance status
clist.Sheets("ZTRE128C").AutoFilterMode = False
lastRow = clist.Sheets("ZTRE128C").Cells(Rows.Count, "C").End(xlUp).Row
Set temp = clist.Sheets("ZTRE128C").Range("C3:S" & lastRow)
temp.Copy
target.Sheets("Countries").Range("D3").PasteSpecial Paste:=xlPasteValues
target.Sheets("Countries").Range("D3:D" & lastRow) = Application.Trim(target.Sheets("Countries").Range("D3:D" & lastRow).Value)
target.Sheets("Countries").Range("D3:D" & lastRow).NumberFormat = "@"
target.Sheets("Countries").Range("T3:T" & lastRow) = Application.Trim(target.Sheets("Countries").Range("T3:T" & lastRow).Value)
For i = 0 To rowz - 1
pay = Application.Index(target.Sheets("Countries").Range("G3:G" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))
fact = Application.Index(target.Sheets("Countries").Range("T3:T" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))
If IsNumeric(pay) Then
target.Sheets("TEST").Range("F" & 7 + i) = pay
ElseIf Left(pay, Len(pay) - 1) = "0" Then 'HERE IS WERE THE ERROR HAPPENS
target.Sheets("TEST").Range("F" & 7 + i) = 1
Else: target.Sheets("TEST").Range("F" & 7 + i) = Left(pay, Len(pay) - 1)
End If
If IsEmpty(fact) Then
target.Sheets("TEST").Range("G" & 7 + i).Value = ""
Else: target.Sheets("TEST").Range("G" & 7 + i).Value = ChrW(&H2713)
End If
Next i
我还注意到,当出现错误时,代码只会遍历整个For
语句一次......
我还想说,我对编码和 VBA 比较陌生,对于可能在此代码中编写的任何可憎之处,提前抱歉。