當多個SUMIFS嵌套在IF陳述句中時，VBA深入研究相關的SUMIFS

再會，

我有一個 VBA 腳本，它允許我深入到包含 SUMIFS 的單元格，然后過濾原始資料表以隔離相關行。該腳本在只有一個 SUMIFS 的單元格上完美運行。但是，我的一些單元格包含一個帶有兩個 SUMIFS 的 IF 陳述句（取決于 IF 是什么變數）。

我一直在試圖找到一種編輯 VBA 的方法，首先在 IF 陳述句中找到相關的 SUMIFS，然后使用正確的進行過濾。

我的代碼：

1. 雙擊作業表上加載的腳本以觸發宏（這作業正常）

   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   FilterBySUMIFs Target.Cells(1)
   End Sub
   

2. 為 SUMIFS 加載到模塊的腳本

   Option Explicit
   
   Sub FilterBySUMIFs(r As Range)
   
   Dim v, ctr As Integer
   Dim intField As Integer, intPos As Integer
   Dim strCrit As String
   Dim rngCritRange1 As Range, rngSUM As Range
   Dim wksDataSheet As Worksheet
   
   If Not r.Formula Like "*SUMIFS(*" Then Exit Sub
   
   'split formula by comma, strip the right paren
   v = Split(Left(r.Formula, Len(r.Formula) - 1), ",")
   
   'the first criteria range is the 2nd element of the array
   Set rngCritRange1 = Range(v(LBound(v)   1))
   
   'use first criteria range to get a reference to the data sheet
   With rngCritRange1
   Set wksDataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
   End With
   
   'clear any existing filter, turn filtering on if needed
   With wksDataSheet
   If .AutoFilterMode And .FilterMode Then
   'clear existing autofilter
   .ShowAllData
   ElseIf Not .AutoFilterMode Then
   'display autofilter arrows
   rngCritRange1.CurrentRegion.AutoFilter
   End If
   End With
   
   'set the filters
   For ctr = LBound(v)   1 To UBound(v)
   If ctr Mod 2 <> 0 Then
   With wksDataSheet
   'determine field in case table does not start in column A
   intField = .Range(v(ctr)).Column - .AutoFilter.Range.Columns(1).Column   1
   'use evaluate instead of range(v(ctr   1))
   'so both cell-reference and hard-coded criteria are handled.
   strCrit = Evaluate(v(ctr   1))
   .Range(v(ctr)).AutoFilter Field:=intField, Criteria1:=strCrit
   End With
   End If
   Next
   
   
   'strip left paren and everything to left of it, get the sum range from first element of array
   intPos = InStr(1, v(LBound(v)), "(")
   Set rngSUM = Range(Replace(v(LBound(v)), Left(v(LBound(v)), intPos), ""))
   'select the SUM range so total displays in status bar
   Application.Goto rngSUM
   
   ActiveWindow.ScrollRow = 1
   
   End Sub
   
   Sub KV_FilterBySumIf()
   
   End Sub
   

我的 SUMIFS 如下所示：

=IF($C$6="ALL",SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14),SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6))

uj5u.com熱心網友回復：

我想出了一個函式，可以將 IF 公式拆分為 TRUE 和 FALSE 部分，并根據運算式回傳相關部分。因此，如果運算式為真，該函式將回傳 IF 公式的真部分。

我所做的函式不是一個健壯的函式，它只有在給定的公式是“=IF(<運算式>, SUMIFS(...), SUMIFS(...))”的結構時才有效。它使用ActiveSheet.

Sub Example()
    Dim SumIfsFormula As String
    SumIfsFormula = "=IF($C$6=""ALL"",SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14),SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6))"

    Debug.Print RelevantSumIfs(SumIfsFormula)
    'Output when TRUE : SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14)
    'Output when FALSE : SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6)
End Sub

Function RelevantSumIfs(SumIfsFormula As String) As String
    Dim IfResult As Boolean
    IfResult = Application.Evaluate("=" & Split(Split(SumIfsFormula, "(")(1), ",")(0))
    
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "SUMIFS\([^)] \)"
    End With
    
    Dim Matches As Object
    Set Matches = regex.Execute(SumIfsFormula)
    
    Dim TargetSumIfs
    If IfResult Then
        TargetSumIfs = Matches(0)
    Else
        TargetSumIfs = Matches(1)
    End If
    
    RelevantSumIfs = TargetSumIfs
End Function

可以通過將 Regex 模式從顯式搜索更改為SUMIFS搜索任何函式來改進此功能。類似的東西.Pattern = "[A-Za-z0-9] \([^)] \)"。但這也可能與公式的許多其他部分相匹配，包括周圍的IF(...). 這就是為什么我把它留為.Pattern = "SUMIFS\([^)] \)"

標籤：擅长 vba

上一篇：使用通配符*符號的新行的VBA代碼

下一篇：VBA：如果特定作業表存在，如何保護它們？