VBA Drill down into relevant SUMIFS when multiple SUMIFS are nested in an IF statement-CodePudding

Good day,

I have a VBA script which allows me to drill down into a cell containing a SUMIFS, and then filters the raw data sheet to isolate the rows pertaining. The script works perfectly on a cell that has just one SUMIFS. However, some of my cells contain an IF statement with two SUMIFS (depending on what variable the IF is).

I am stuck trying to find a way to edit the VBA to first find the relevant SUMIFS in the IF statement, and then use the correct one to then filter.

My code:

Double click script loaded on worksheet to trigger the macro (this is working fine)

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

Script loaded to Module for the 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

My SUMIFS looks as follows:

=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))

CodePudding user response：

I came up with a function that can split the IF formula into it's TRUE and FALSE parts and return relevant part based on the expression. So if the expression is true, the function returns the True part of the IF Formula.

The function I have made is not a robust function, and it only works if the given formula is in the structure of "=IF(< expression >, SUMIFS(...), SUMIFS(...))". And it evaluates the expression with the 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

This function could be improved by changing the Regex pattern from explicitly searching for SUMIFS to searching for any function. Something like .Pattern = "[A-Za-z0-9] \([^)] \)". But this may also match many other parts of the formula, including the surrounding IF(...). Which is why I left it as .Pattern = "SUMIFS\([^)] \)"