VBA to jump to range in contained in a SUMIFS formula

[Silverjman]

Hello,

Once you unchecked "Edit in Cell" in options the double click jump to the sum range in the first part of the SUMIFS function is FANTASTIC for transparency, but what if for calculation speed reasons you need to put IFs in front of the SUMIFS instead of Booleans at the back?

You lose the Ctrl { jump as well as the double click jump.

I have had a really tough time Googling this because the keywords involved create a lot of noise. If anyone has failsafe keywords or the first bit of this code I would be very grateful.

In a perfect world I would end up with code that not only BeforeDoubleClick event jumps to the right sum_range but to the group dictated by the IFs in a SUMIFS.

As I said I realise this is a genius level query, so even the smallest amount of help would be appreciated.

Thanks

[Aussiebear]

Post a sample workbook please.

[SamT]

In a perfect world I would end up with code that not only BeforeDoubleClick event jumps to the right sum_range but to the group dictated by the IFs in a SUMIFS.

Have VBA capture Excel's double click and If the Target HasFormula, Then, If the formula contains SUMIF()'s, Then, parse the formula and display some to-be-decided part of the table referred to in the first SUMIF() in the formula.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim StartPosition As Long

If Not HasFormula(Target) Then Exit Sub

StartPosition = InStr(Target.Formula, "SUMIF")
If StartPosition = 0 Then Exit Sub

Cancel = True

'Parse Formula and continue

End Sub