VBA：使用INDEX MATCH循环无法进入下一个值

Question

我是一个VBA新手...感谢您的帮助 :)

我有一个像这样的表：

S/N     UNIQREF   REF   OPE
ABC123             10    C1
ABC123             20    C2
ABC123             30    I1
ABC123             40    IM1
DEF456             10    C1
DEF456             20    I1
DEF456             30    DR1
HIJ789             10    C1
HIJ789             20    DR1
HIJ789             30    I1
HIJ789             40    XX1

...我需要填写UNIQREF列。

规则是：遍历我的S/N（序列号）列，只要它保持不变，查找“I1” OPE（操作）左侧的值，并在所有行中将其写入UNIQREF。

例如，我们取ABC123，在OPE中查找“I1”，看到它旁边（左侧）的值为30，因此我们为所有ABC123 S/N编写30个UNIQREF列。

同样，我们会为所有DEF456 S/N编写20，并为所有HIJ789 S/N编写30个UNIQREF。

到目前为止，这是我的代码：

Public Sub routine()

Dim gamWAL as Worksheet
Set gamWAL = ThisWorkbook.Sheets("gamWAL")

i = 3 'because data in my table starts on line 3, line 2 is columns titles and line 1 is comments. Also starts in Column B (my S/N column)...

Do While gamWAL.Cells(i, "B").Value <> "" 'i want the loop to stop at the when the S/N list is over

    Do While gamWAL.Cells(i, "B").Value = gamWAL.Cells(i + 1, "B").Value

        gamWAL.Cells(i, "E").Value = Application.WorksheetFunction.Index(gamWAL.Range("F" & i & ":G3000"), Application.WorksheetFunction.Match("I1", gamWAL.Range("G" & i & ":G3000"), 0), 1)

        i = i + 1

    Loop

Loop

End Sub

问题: 对于所有的ABC123序列号，它都写入了"20"，除了最后一个，在下一组序列号之前停止了...尽管宏一直在 "计算"(因为表格目前有900行，但预计将增长到约4000行)。

有什么建议可以让它正常工作吗？

谢谢！

- Zonure

Answer 1

尝试，

=INDEX(C:C, AGGREGATE(15, 7, ROW($2:$12)/((A$2:A$12=A2)*(D$2:D$12="I1")), 1))

假设REF是数字，I1对于每个S/N都是唯一的，那么SUMIFS同样也可以，并且更加灵活和友好，适用于记录删除/添加。

=sumifs(c:c, a:a, a2, d:d, "I1")

- user4039065

---

那是一些非常神秘的事情。 - CLR

SUMIFs 的想法很好。 - SJR

sumifs 这个东西就像巫术一样，它非常好用！你能否用简单明了的语言解释一下它实际是在做什么？因为我不理解我们正在求和的是什么... 谢谢你！ - Zonure

你正在对单个数字进行“求和”操作；具体来说，是在列D为I1且列A与列A匹配的行中的单个数字。 - user4039065

Answer 2

另一种公式方法，尽管无疑不如Jeeped的效率高，因为它是一个数组公式（使用Ctrl+Enter+Shift将显示花括号）。

=INDEX($C$2:$C$12,MATCH(1,IF($A$2:$A$12=$A2,IF($D$2:$D$12="I1",1)),0))

有一种非数组的变体，请参见http://www.xl-central.com/lookup-multiple-criteria.html

- SJR

Answer 3

这是一种可能的VBA解决方案，需要经过2步并使用替换。

第一步：

循环遍历Range("A1:A13")中的所有单元格，并查看列D的值是否为I1。如果是，则写入参考列中的值，否则写入与SN相同的值。

以下是第一步得到的结果：

步骤2：

• 循环遍历相同范围，每当UNIQREF相同时，进行替换。
• 替换应仅在UNIQREF列中进行。

这是两个步骤的代码：

Public Sub TestMe()

    Dim startCell As Long: startCell = 2
    Dim endCell As Long: endCell = 13

    Dim cnt As Long, cnt2 As Long
    Dim myCell As Range
    Dim lookFor As String: lookFor = "I1"
    Dim wks As Worksheet: Set wks = Worksheets(1)

    For cnt = startCell To endCell
        Set myCell = wks.Cells(cnt, 1)
        If myCell.Offset(, 3) = lookFor Then
            myCell.Offset(, 1) = myCell.Offset(, 2)
        Else
            myCell.Offset(, 1) = myCell
        End If
    Next cnt

    For cnt = startCell To endCell
        Set myCell = wks.Cells(cnt, 1)
        If myCell <> myCell.Offset(, 1) Then
            wks.Range(wks.Cells(startCell, 2), wks.Cells(endCell, 2)).Replace _
                What:=myCell, Replacement:=myCell.Offset(, 1), LookAt:=xlPart
        End If
    Next cnt

End Sub

- Vityata