Excel: 以leadtime判斷是否有風險

有個小需求, 若在leadtime天數內, 有任一週的數量小於0, 就要判斷為Risk, 否則為Safe.

單純用既有function似乎不好作, 試了VBA可以達成.

 Figure 1.

     Dim arr As Variant
    ' read cell range to array
    'arr = 工作表1.Range("D2:K2").Value
   
    Dim name As Variant, cnt As Long
   
    ' Go through the array
    For i = 2 To 8
   
      cnt = 0
      arr = 工作表1.Range(Cells(i, 4), Cells(i, 8)).Value
   
      For Each name In arr
        
        If cnt >= 工作表1.Cells(i, 3).Value / 7 Then
          Exit For
        End If
       
        If name < 0 Then
            工作表1.Cells(i, 2) = "Risk"
            Exit For
        Else
            工作表1.Cells(i, 2) = "Safe"
        End If
        cnt = cnt + 1
      Next name
   Next i
End Sub