Sub D正15打PS16()
Dim i As Integer, j As Integer, k As Integer
Dim eod1 As Integer, eod2 As Integer, eod3 As Integer, eod4 As Integer, Result1 As Integer
Dim sum0 As Integer
Dim summ As Integer
Dim sum2 As Integer
eod1 = InputBox("請輸入需判斷資料開始列數?")
eod2 = InputBox("請輸入需判斷資料結束列數?")
eod3 = InputBox("請輸入需判斷資料起始行數 ?")
eod4 = InputBox("請輸入需判斷資料結束+1 行數 ?")
Result1 = InputBox("請輸入需判斷結果儲存行數 ?")
For i = eod1 To eod2
sum0 = 0
summ = 0
sum2 = 0
For j = eod3 To eod4
summ = summ + (Cells(i, j).Value)
If summ >= 15 Then
sum2 = 0 '判斷前段是否有LB'
For k = eod3 To j
If (Cells(i, k).Value) >= 6 Then
sum2 = sum2 + 1
Else
End If
Next k
If (Cells(i, j + 1).Value) >= 6 Then
sum0 = sum0 - 120 - 1
Cells(i, Result1) = sum0
Cells(i, Result1 + 2) = "後段LB" '顯示後段有LB'
If sum2 = 0 Then '顯示前段是否有LB'
Cells(i, Result1 + 1) = "N"
ElseIf sum2 = 1 Then
Cells(i, Result1 + 1) = "LB"
ElseIf sum2 = 2 Then
Cells(i, Result1 + 1) = "LBB"
ElseIf sum2 = 3 Then
Cells(i, Result1 + 1) = "LBBB"
ElseIf sum2 = 4 Then
Cells(i, Result1 + 1) = "LBBBB"
Else
End If
Exit For
Else
sum0 = sum0 + Abs(Cells(i, j + 1).Value)
End If
summ = 100
If Abs(Cells(i, j).Value) = 0 Then '判斷結束?'
If (Cells(i, j - 1).Value) = 1 Then '修正尾數-虧損-第一顆-原加上的'
Cells(i, Result1) = sum0 - 1 - 1 - 1
ElseIf (Cells(i, j - 1).Value) = 2 Then
Cells(i, Result1) = sum0 - 4 - 1 - 2
ElseIf (Cells(i, j - 1).Value) = 3 Then
Cells(i, Result1) = sum0 - 11 - 1 - 3
ElseIf (Cells(i, j - 1).Value) = 4 Then
Cells(i, Result1) = sum0 - 26 - 1 - 4
ElseIf (Cells(i, j - 1).Value) = 5 Then
Cells(i, Result1) = sum0 - 57 - 1 - 5
Else
End If
Cells(i, Result1 + 2) = "後段N" '顯示後段有LB'
If sum2 = 0 Then '顯示前段是否有LB'
Cells(i, Result1 + 1) = "N"
ElseIf sum2 = 1 Then
Cells(i, Result1 + 1) = "LB"
ElseIf sum2 = 2 Then
Cells(i, Result1 + 1) = "LBB"
ElseIf sum2 = 3 Then
Cells(i, Result1 + 1) = "LBBB"
ElseIf sum2 = 4 Then
Cells(i, Result1 + 1) = "LBBBB"
Else
End If
End If
End If
Next j
Next i
End Sub
