Range.Formula and Range question using Excel Automation

[Alfred]

Using Excel Automation, I'd like to try and open up an Excel file and
locate any cell which is summing the columns above it.

My current train of though was to cycle through all the cells and look
at Range.Formula, searching for a "=sum". However, this is not exact
enough as I could have a cell in a row which is totalling the rows
contents rather than the column itself. In which case I'd like to
continue searching.

That led me to the idea when I found a "=sum", to look at the
parameter of the sum() function and check if that range is located in
the column of the cell. If it is, then I would be assured the cell is
summing the column.

Any ideas or suggestions on other ways of tackling the problem?
Quicker methods?

A snippet of my code follows.

---------

for(int i = rows.Count; i > 0; i--)
{
for(int k = 1; k < columns.Count + 1; k++)
{
cell = (Excel.Range)range.Cells[i,k];

if(((string)cell.Formula).ToLower().StartsWith("=sum"))
{
// we have some type of formula in this cell, do some processing

}
}
}

 

[d.winkler]

I can't think of a way other than to evaluate the formula as you are
already doing.

Regarding your method for finding "sum", any reason you are not using
the VBA ".Find" (and FindNext) to speed up your search?

Below is the example shown in Help.
I've used it many times with "lookin:=xlValues", but not for
xlFormulas (which I think is how you'd do it); but it should work.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With