excel - 根据给定条件的总和范围从一列返回值

Question

我有一个包含列的表A，B并且C列中的所有值都按升序排列。Column A有序列号，column B有名称，column C有数量。

当我输入一个名称，例如 incell L2和cell M2所需数量时，假设 540 宏应该从cell L2in 中搜索 namecolumn B并对该名称的所有值求和，直到 sum 与 from 的值匹配cell M2。column A当 sum 匹配时，复制（返回）该 sum 范围内的所有序列号in column N。

我手动做了一个例子，它应该是什么样子。

我在另一个论坛上提出了同样的问题，但没有答案（https://www.mrexcel.com/board/threads/reverse-sumif-return-column-values-based-on-sum-criteria.1179552/）

标签： excelvba

Answer 1

看看这对你有什么作用：

Sub GetSerialNo()

Dim ws As Worksheet, lRowInput As Long, lRowResults As Long, i As Long
Dim arr, SName As String, SQuantity As Double, CurQuantity As Double
Dim MatchList(), MatchCount As Long

Set ws = Sheets("Blanko List") 'Your sheet name
lRowInput = ws.Range("A" & Rows.Count).End(xlUp).Row 'Last row of data
lRowResults = ws.Range("N" & Rows.Count).End(xlUp).Row 'Last row of previous results
arr = ws.Range("A2:C" & lRowInput).Value 'Populate the array
SName = ws.Range("L2").Value 'Search name
SQuantity = ws.Range("M2").Value 'Search Quantity
CurQuantity = 0 'Ensure these 2 values are 0
MatchCount = 0

For i = 1 To UBound(arr, 1) 'Loop from row 1 to last row
    If arr(i, 2) = SName Then 'If name = search name
        If arr(i,3) > 0 Then 'New line to skip quantities of 0
            CurQuantity = CurQuantity + arr(i, 3) 'add quantity
            MatchCount = MatchCount + 1 'Add match count
            ReDim Preserve MatchList(1 To MatchCount) 'Resize the matchlist array to add a new row
            MatchList(UBound(MatchList)) = arr(i, 1) 'Add the name to new row of matchlist
            If CurQuantity >= SQuantity Then Exit For 'If the quantity is equal or greater than search quantity then exit the loop
        End if 
    End If
Next i

ws.Range("N2:N" & lRowResults + 1).ClearContents 'Clear previous results list
ws.Range("N2").Resize(UBound(MatchList)).Value = Application.Transpose(MatchList) 'Dump new results into column N
If CurQuantity < SQuantity Then
    MsgBox "The available quantity is less than the desired quantity." & vbCr & vbCr & _
    "Desired: " & SQuantity & vbCr & "Available: " & CurQuantity & vbCr & _
    "Difference: " & CurQuantity - SQuantity, vbExclamation, "Missing Quantities"
End If

End Sub

我已经在每一行中添加了注释，这样你就可以有希望地跟随它正在做的事情。

如果可用数量少于所需数量，我也刚刚添加了检测。您显然可以将消息框所说的内容更改为您想要的内容。