I am working on automating a report with VBA, the code is working relatively well except for one of my If statements. In fact it works once every other time, and when it doesn't I get a mismatch error. After trying to resolve this all morning, I think that there may be an issue with the variable types I'm using or the fact that I use a text format at some point.
Here's the part where I am having problems, the error, when it pops up, does so at the ElseIf statement marked below
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
I also noticed that when the error appears the code goes through the whole For statement just once...
I would also like to say that I am relatively new to coding and VBA, sorry in advance for any abominations that may be written in this code.
Aucun commentaire:
Enregistrer un commentaire