mardi 30 octobre 2018

Run Time Error 91 in VBA even with correct date

There were a lot of questions and answers on this error, but none seemed to resolve my issue. I have a workbook of multiple worksheets. My goal is to match up lines in two of the worksheets and print some of the data onto a blank worksheet.

I need 5 of the cells to match from one sheet to the next so I used an If statement with multiple conditions. It works fine when the criteria is met, but when it's not , it doesn't move onto the Else statement. Instead it gives me the error 91.

Sub Novar_RSL_Filter()

'Locate any orders that match on the RSL and Discrepancy Report
'Also print out any orders that are on the RSL but not the discrepancy 
report

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim I, count As Integer
Dim found1, found2, found3, found4, found5 As Range

Set ws1 = Worksheets("Discrepancy Report")
Set ws2 = Worksheets("Required Spot Listing")
Set ws3 = Worksheets("Results")

'defines last row to check
Total = ws2.Range("I" & Rows.count).End(xlUp).Row

count = 2
For I = 2 To Total
    clientID = ws2.Range("C" & I).Value 'Get clientID value
    contractID = ws2.Range("E" & I).Value 'Get contract #
    Zone = ws2.Range("G" & I).Value 'Get Zone name
    StartDate = ws2.Range("Q" & I).Value 'Get date of event
    StartTime = ws2.Range("R" & I).Value 'Get start time of event
    Set found1 = ws1.Columns("J:J").Find(what:=clientID) 'Check for client ID in Dicrepancy Report
    Set found2 = ws1.Columns("L:L").Find(what:=contractID) 'Check for contract ID in Dicrepancy Report
    Set found3 = ws1.Columns("H:H").Find(what:=Zone) 'Check for zone in Dicrepancy Report
    Set found4 = ws1.Columns("E:E").Find(what:=StartDate) 'Check for date in Dicrepancy Report
    Set found5 = ws1.Columns("F:F").Find(what:=StartTime) 'Check for start time in Dicrepancy Report
    If (found1 = clientID) And (found2 = contractID) And (found3 = Zone) And (found4 = StartDate) And (found5 = StartTime) Then
        ws3.Range("A" & count).Value = ws2.Range("D" & I).Value
        ws3.Range("B" & count).Value = ws2.Range("E" & I).Value
        ws3.Range("D" & count).Value = ws2.Range("AA" & I).Value
        ws3.Range("E" & count).Value = ws2.Range("Q" & I).Value
        ws3.Range("F" & count).Value = "Yes" 'set Discrepancy report column for yes
        count = count + 1
    Else
        ws3.Range("A" & count).Value = ws2.Range("D" & I).Value
        ws3.Range("B" & count).Value = ws2.Range("E" & I).Value
        ws3.Range("D" & count).Value = ws2.Range("AA" & I).Value
        ws3.Range("E" & count).Value = ws2.Range("Q" & I).Value
        ws3.Range("F" & count).Value = "No" 'set Discrepancy report column 
          for no
        count = count + 1
    End If
Next I

End Sub

The error occurs when I get to the If statement and it only occurs when something can't be found. Which is fine, it just means I want it to go to the Else statement, but it keeps throwing the error.

Thanks in advance for your help!

Aucun commentaire:

Enregistrer un commentaire