The code below works through the first two loops then I get a Run-time error '13': Type mismatch (shown red text). If I remove the DueStatus portion of the two loops that it errors in the remaining 6 loops deliver the desired data and the two modified loops follow the remaining code. I'm lost on this problem.
Sub OVERDUE_DWO()
Dim x As Integer
Dim area As String
Dim PastDue As Long
Dim i As Long
Dim status As String
Dim workType As String
Dim DueStatus As String
For x = 1 To 9
area = Sheets("DWOs").Cells(x, 1).Value
PastDue = 0
If area = "FAC PROCESS MAINT" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC GENERAL MAINT" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC ELECTRICAL" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC FIRESYS" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC FMCS" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC BALANCE" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FAC PREDICTIVE" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
ElseIf area = "FIRE PROTECTION" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue
End If
Next x
End Sub