excel - 将第一个值存储在序列中，并在条件满足时使用存储的值

Question

我有这张表，VBA 代码必须从主要值为是的产品中获取值。但是，值必须是序列中的第一个值。

+---------+---------+---------+
| Product | Results | Primary |
+---------+---------+---------+
| A       |         |         |
| B       |         | Yes     |
| C       |         | Yes     |
| D       |         |         |
| E       |         | Yes     |
| F       |         |         |
| G       |         | Yes     |
| H       |         | Yes     |
| I       |         |         |
+---------+---------+---------+

预期结果:

+---------+---------+---------+
| Product | Results | Primary |
+---------+---------+---------+
| A       |         |         |
| B       | A       | Yes     |
| C       | A       | Yes     |
| D       |         |         |
| E       | D       | Yes     |
| F       |         |         |
| G       | F       | Yes     |
| H       | F       | Yes     |
| I       |         |         |
+---------+---------+---------+

我在 vba 代码下尝试过这个，但没有按我预期的那样工作。

Sub test()
    Dim i As Long
    Dim lr As Long

    lr = ActiveSheet.UsedRange.Rows.Count

    For i = 2 To lr
        If Range("D" & i).Value = "Yes" Then
            Range("C" & i).Value = Range("B" & i - 1).Value
        End If
    Next
End Sub

Answer 1

Excel公式

这是您的公式外观的示例。这首先查看主字段是否为“ 是 ”。如果是，那么它检查先前的结果是否也是肯定的，如果是，则捕获它的结果。否则，它会根据您的示例获取第一个值。

=IF($C2="Yes", IF($C1="Yes", $B1, $A1),"")

根据需要进行调整!

VBA 代码

我建议创建一种查找标题的方法，这样可以更容易地更新，以防万一它发生变化或添加了更多字段。下面我添加了一个我将尝试做的示例。

我使用了一个辅助函数(Inject)来构建实际的公式，并在出现问题时使其更容易阅读/调试。

也可以随意添加您自己的自定义错误处理。

Just note this is one of many ways you could tackle this. I could even break this formula into even smaller components to abstract as much of it as possible.

Option Explicit

Private Sub AddResultsToTable()

    Dim Ws As Worksheet
    Set Ws = ActiveSheet

    'FIND COLUMN HEADERS TO USE IN FORMULA REFERENCES
    With Ws.UsedRange

        On Error GoTo Catch
        Dim Product As Range
        Set Product = .Find("Product")

        Dim Results As Range
        Set Results = .Find("Results")

        Dim Primary As Range
        Set Primary = .Find("Primary")

    End With

    'CREATE FORMULA. Example: =IF($C2="Yes", IF($C1="Yes", $B1, $A1),"")
    Dim CustomFormula As String
    CustomFormula = Inject("=IF(${0}='Yes', IF(${1}='Yes', ${2}, ${3}),'')", _
        Primary.Offset(1).Address(False, True), _
        Primary.Address(False, True), _
        Results.Address(False, True), _
        Product.Address(False, True) _
    )

    'SET FIRST RANGE EQUAL TO FORMULA & AUTOFILL FORMULA DOWN
    With Results.Offset(1)
        .Value = CustomFormula
        .AutoFill Range(.Address, Ws.Cells(Ws.Rows.Count, Product.Column).End(xlUp).Offset(, 1))
    End With

Exit Sub
Catch:
    'You can do your error handling here.
    MsgBox Err.Description, vbCritical

End Sub


'METHOD THAT ALLOWS A STRING TO BE REPLACED WITH VARIABLES AND SPECIAL CHARACTERS
Public Function Inject(ByVal Source As String, ParamArray Args() As Variant) As String

    '@AUTHOR: ROBERT TODAR
    '@EXAMPLE: Inject("${0}, ${1}!", "Hello", "Robert") --> Hello, Robert!

    'REPLACE SINGLE QUOTES WITH DOUBLE QUOTES
    Inject = Source
    Inject = Replace(Inject, "'", """")

    'REPLACE ${#} WITH VALUES STORED IN THE VALUE IN THAT INDEX.
    Dim Index As Integer
    For Index = LBound(Args, 1) To UBound(Args, 1)
        Inject = Replace(Inject, "${" & Index & "}", Args(Index), , , vbTextCompare)
    Next Index

End Function