Determine cel contains formula '=sum()'

[pvr928]

Hi

I'm trying to test if a cell contains '=Sum()' (the cells in question will only contain this formula, another formula or a hard coded value.

I am using this UDF:

Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 4) = "=SUM"
End Function

I am getting a 'Run time error 1004' 'Method range of object _global' failed on the 'IsFormula = ... ' line in the UDF

I am referencing this UDF in another Module using:

Set rCellRef = Outputws.Cells(StartCellRow + r - 1, StartCellCol)

If IsFormula(rCellRef) = False Then ...

All of the other references are working and the code is compiling properly up until it hits the UDF.

Any thoughts on what might be causing this?

Cheers

pvr928

 

[Marcelo Branco]

As rCellRef is

Set rCellRef = Outputws.Cells(StartCellRow + r - 1, StartCellCol)

it seems that the code is passing a range object to the function, not a string in
If IsFormula(rCellRef) = False Then

So, see if this works

[COLOR=#333333]Function IsFormula(cellref)[/COLOR]
[COLOR=#333333]      IsFormula = Left([/COLOR][COLOR=#0000ff]cellref.Formula[/COLOR][COLOR=#333333], 4) = "=SUM"[/COLOR]
[COLOR=#333333]End Function
[/COLOR]

It worked for me

Hope this helps

M.

 

[pvr928]

Thanks so much Marcelo - works a treat now!

Cheers

pvr928

 

[Marcelo Branco]

You welcome. Glad to help

M.