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