excel - 满足一定条件的单元格字符串的总和

Question

我只是想知道如何在 vba 中计算这个:
如果是则计算第一笔金额，如果不是则不计算金额。假设有四个单元格:

(cell 1) abcbc bcbcbcb cbcbcbc $1000/kskskksks/$2000//1222/1221/11/yes  
(cell 2) any words will be here $2300/heyhey hey/ //3232//3232/no  
(cell 3) kakjsak  dsdsk kdjskj 2323/ $23232/hhehe 22/33/333/yes  
(cell 4) kakaka kjsdkj ksjskjds kdjsjkdj 11 223 222/ $1121/ $2121/yes

该算法是检查是或否。然后，在每一行上找到第一笔钱，以 $ 开头，同一行上的第二笔钱不考虑在内。

在此示例中，程序将考虑 $1000，因为它是 yes，第二行不会执行，因为它是 no。第三个单元格将获取第一笔钱(第一个 $)，$23232。因此，程序将计算 $1000+$23232+$1121=$25353

Answer 1

考虑到您使用第一列来放置每个值并且工作表的名称是“Sheet1”，我猜这就是您想要的

Sub SumFirstAmountIfYes()

    Dim AmountSum As Variant ' Declares the AmountSum

    lastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row ' Finds the last used row on the first column

    For i = 1 To lastRow ' Iterates over the rows to the last row
        StringValue = Sheets("Sheet1").Cells(i, 1).Value2 ' Gets the value to a variable
        If StringValue Like "*yes" Then ' Checks if the string terminates with "yes"
            FirstDollar = InStr(StringValue, "$") ' Finds first dollar symbol "$"
            FirstSlashAfterDollar = InStr(FirstDollar, StringValue, "/", 0) ' Finds first slash "\" after the first dollar symbol

            FirstAmount = Mid(StringValue, FirstDollar + 1, FirstSlashAfterDollar - FirstDollar - 1) ' Gets the amount of each row

            AmountSum = AmountSum + CDec(FirstAmount) ' Adds to the sum variable each found amount
        End If
    Next

    MsgBox (AmountSum) ' Shows the final sum of the amounts

End Sub