vendredi 17 août 2018

Error Type 13, Mismatch, for If Statement half of the time

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