1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
|
Function Look(strWhat As String, strSheet As String, strRange As String)
Dim celF As Range
With Worksheets(strSheet).Range(strRange)
Set celF = .Find(strWhat, Lookat:=xlWhole)
If Not celF Is Nothing Then
Look = "LookOK"
Else
Look = "LookKO"
End If
End With
End Function
Function CalculateStatus(ASRType As String, ASRState As String, ASRCN As String, ASRPbLinked As String, CN_state As String)
Dim strResult As String
Dim ASRCN_values As Variant
Dim I As Integer
Dim test As String
strResult = "not calculated"
If ASRType = "ASR Problem" Then
If ASRState = "Open" Or ASRState = "Assigned" Or ASRState = "In Progress" Or ASRState = "Pending Customer" Or ASRState = "Waiting for Approval" Then
strResult = "In progress"
Else
If ASRState = "Waiting" Then
If ASRCN = "" Then
strResult = "ERROR1"
Else
ASRCN_values = Split(ASRCN, ", ")
If UBound(ASRCN_values) > 0 Then
For I = 0 To UBound(ASRCN_values)
If strResult = "Waiting for CN resolution" Then Exit For
test = ASRCN_values(I)
If Look(test, "ASR_CN", "B:B") = "LookKO" Then
strResult = "ERROR2"
' ERROR2 means that:
Else
strResult = "Waiting for CN resolution"
End If
Next
Else
test = ASRCN_values(0)
If Look(test, "ASR_CN", "B:B") = "LookKO" Then
strResult = "ERROR2"
' ERROR2 means that:
Else
strResult = "Waiting for CN resolution"
End If
End If
End If
Else
If ASRState = "Resolved" Or ASRState = "Closed" Then
If ASRCN = "" Then
strResult = "Closed without CN"
Else
If Range("ASR_CN!B:B").Find(ASRCN) Is Nothing Then
strResult = "ERROR3"
' ERROR3 means that:
Else
If CN_state = "Rejected" Then
strResult = "Closed without CN"
Else
strResult = "Closed with CN resolution"
End If
End If
End If
End If
End If
End If
Else
If ASRType = "ASR Incident" Then
If ASRState = "Open" Or ASRState = "Assigned" Or ASRState = "In Progress" Or ASRState = "Pending Customer" Then
strResult = "In progress"
Else
If ASRState = "Waiting" Then
If ASRPbLinked = "" Then
strResult = "ERROR5"
'ERROR5 means that:
Else
Dim ASRPbLinked_state As Variant
ASRPbLinked_state = Application.VLookup(ASRPbLinked, Range("B:W"), 22, False)
If ASRPbLinked_state = "Waiting for CN resolution" Then
strResult = "Waiting for CN resolution"
Else
If ASRPbLinked_state = "In progress" Then
strResult = "Linked to an opened Problem"
Else
If ASRPbLinked_state = "Closed without CN" Or ASRPbLinked_state = "Closed with CN resolution" Then
strResult = "ERROR6"
'ERROR6 means that:
Else
If ASRPbLinked_state = "ERROR1" Or ASRPbLinked_state = "ERROR2" Or ASRPbLinked_state = "ERROR3" Then
strResult = "ERROR7"
'ERROR7 means that:
End If
End If
End If
End If
End If
Else
If ASRState = "Resolved" Or ASRState = "Closed" Then
If ASRPbLinked = "" Then
strResult = "Closed without Problem"
Else
Dim ASRPbLinked_status As Variant
Dim ASRPbLinked_CN As Variant
ASRPbLinked_status = Application.VLookup(ASRPbLinked, Range("B:C"), 2, False)
ASRPbLinked_CN = Application.VLookup(ASRPbLinked, Range("B:K"), 10, False)
If ASRPbLinked_status = "Resolved" Or ASRPbLinked_status = "Closed" Then
If ASRPbLinked_CN = "" Then
strResult = "Closed without CN"
Else
If Range("ASR_CN!B:B").Find(ASRPbLinked_CN) Is Nothing Then
strResult = "ERROR3"
' ERROR3 means that:
Else
If CN_state = "Rejected" Then
strResult = "Closed without CN"
Else
strResult = "Closed with CN resolution"
End If
End If
End If
Else
strResult = "ERROR4"
' ERROR4 means that:
End If
End If
End If
End If
End If
End If
End If
CalculateStatus = strResult
End Function |
Partager