VBA 自定义函数 库存消耗截止

两个自定义函数

1. 按照库存，计算哪一周库存能消耗完，标出周。自定义函数
2. 按照库存，计算消耗完的那一周实际消耗多少数量。自定义函数
3. 从消耗完的那一周开始算起，后面的周消耗数量都变为0。按钮

Function HaoJin(StockQty As Double, RequireQtyArea, LabelArea)
   Dim RequireColumn As Integer
   Dim RequireTotal As Double
   RequireTotal = 0
   For RequireColumn = 1 To RequireQtyArea.Columns.Count
       RequireTotal = RequireTotal + RequireQtyArea.Cells(1, RequireColumn).Value
       If StockQty - RequireTotal < 0 Then
          HaoJin = LabelArea.Cells(1, RequireColumn).Value
          Exit For
       End If
       If RequireColumn = RequireQtyArea.Columns.Count Then
          HaoJin = "待入单消耗"
       End If
   Next RequireColumn
End Function

Function HaoJin2(StockQty As Double, RequireQtyArea, LabelArea)
   Dim RequireColumn As Integer
   Dim RequireTotal As Double
   Dim LinJieQty As Double
   RequireTotal = 0
   For RequireColumn = 1 To RequireQtyArea.Columns.Count
       RequireTotal = RequireTotal + RequireQtyArea.Cells(1, RequireColumn).Value
       If StockQty - RequireTotal < 0 Then
          HaoJin2 = RequireQtyArea.Cells(1, RequireColumn).Value + (StockQty - RequireTotal)
          Exit For
       End If
       If RequireColumn = RequireQtyArea.Columns.Count Then
          HaoJin2 = "待入单消耗"
       End If
   Next RequireColumn
End Function

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    For i = 3 To 1000
        If Len(Sheet4.Cells(i, 2).Value) = 0 Then
           Exit For
        End If
        For j = 30 To 41
           If Sheet4.Cells(i, 28).Value = Sheet4.Cells(2, j).Value Then
              Sheet4.Cells(i, j).Value = Sheet4.Cells(i, 29).Value
              For k = j + 1 To 41
                  Sheet4.Cells(i, k).Value = 0
              Next k
           End If
        Next j
    Next i
End Sub