|
1学分
Sub 狀態判斷()
Dim i As Long, s()
i = 2
Range("aj2:AJ1048576") = ""
s = Range("a1:AK300000")
Q = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Q Step 1
r = s(i, 37).End(xlToLeft).Column
If s(i, 37) <> "" Then
On Error Resume Next
If s(i, 37) = "Scraped" Or s(i, 37) = "SCRAP" Then
If r = 1 Or r = 35 Or r = 34 Then
s(i, 29) = "Fail"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Scraped"
s(i, 36) = "Scraped"
End If
If r = 29 Or r = 28 Or r = 27 Then
s(i, 29) = "Fail"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Scraped"
s(i, 36) = "Scraped"
End If
If r = 20 Or r = 21 Or r = 22 Then
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Scraped"
s(i, 36) = "Scraped"
End If
If r = 13 Or r = 14 Or r = 15 Then
s(i, 15) = "Fail"
s(i, 8) = "Scraped"
s(i, 36) = "Scraped"
End If
If r = 6 Or r = 8 Or r = 7 Then
s(i, 8) = "Scraped"
s(i, 36) = "Scraped"
End If
End If
'1
On Error Resume Next
If s(i, 37) = "Repair OK" Or s(i, 37) = "KEY OUT" Then
If r = 1 Or r = 35 Or r = 34 Then
s(i, 29) = "Fail"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Pass"
End If
If r = 27 Or r = 29 Or r = 28 Then
s(i, 29) = "Pass"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Pass"
End If
If r = 20 Or r = 21 Or r = 22 Then
s(i, 22) = "Pass"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Pass"
End If
If r = 13 Or r = 14 Or r = 15 Then
s(i, 15) = "Pass"
s(i, 8) = "Fail"
s(i, 36) = "Pass"
End If
If r = 6 Or r = 8 Or r = 7 Then
s(i, 8) = "Pass"
s(i, 36) = "Pass"
End If
End If
On Error Resume Next
If s(i, 37) <> "Repair OK" And s(i, 37) <> "KEY OUT" And s(i, 37) <> "SCRAP" And s(i, 37) <> "Scraped" Then
If r = 1 Or r = 35 Or r = 34 Then
s(i, 29) = "Fail"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Fail"
End If
If r = 27 Or r = 29 Or r = 28 Then
s(i, 29) = "Fail"
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Fail"
End If
If r = 20 Or r = 21 Or r = 22 Then
s(i, 22) = "Fail"
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Fail"
End If
If r = 13 Or r = 14 Or r = 15 Then
s(i, 15) = "Fail"
s(i, 8) = "Fail"
s(i, 36) = "Fail"
End If
If r = 6 Or r = 8 Or r = 7 Then
s(i, 8) = "Fail"
s(i, 36) = "Fail"
End If
End If
End If
Next i
End Sub
|
最佳答案
查看完整内容
把“r = s(i, 37).End(xlToLeft).Column”换为“r = Cells(i, 37).End(xlToLeft).Column”试试。
|