Sumif Precedents VBA

[vikeshjain1981]

Hi everybody

I have a huge workbook (17MB) with 19 sheets. In the summary sheet we have Sumif and Sumifs formulas. Now to review the sheet I need to trace the actual values referred to by those formulas (not the range). I have tried several excel auditing addins (Aaron T. Blood's Explode, JKP Reftreeanalyzer, Dependency auditor, Arixcel explorer etc) - they all refer to general ranges but not the specific values. In the end I found Héctor Miguel code which does what I am looking for - but it has following shortcomings:

1. Works only on Sumif and not Sumifs (back then they didnt had sumifs?)
2. Works only if the range is on the same sheet
3. Works only if the criteria is picked from another cell - if it is hardcoded within the formula (e.g. "A" instead of F4) then it is not working

Can somebody please help me..

Héctor Miguel Code
Sub myMatchingSumif()
Dim theFunction As String, theCriteria As String, theCondition As String, _
theResults As String, theRange As Range, n As Integer, Temp As String, Tmp
If ActiveCell.HasFormula Then theFunction = ActiveCell.Formula Else Exit Sub
If Mid(theFunction, 2, 5) <> "SUMIF" Then Exit Sub
Temp = Mid(theFunction, 8, Len(theFunction) - 8)
Tmp = Split(Temp, ",")
theCriteria = Tmp(LBound(Tmp))
theCondition = Tmp(LBound(Tmp) + 1)
theResults = Tmp(LBound(Tmp) + 2)
With Range(theCriteria)
For n = 1 To .Count
If .Cells = Range(theCondition) Then
Set theRange = Union(IIf(theRange Is Nothing, _
Range(theResults).Cells, theRange), Range(theResults).Cells)
End If
Next
End With
If Not theRange Is Nothing Then theRange.Select: Set theRange = Nothing
End Sub