Hi,

I am trying to replace every sheet cells that contains a
occurence of a specific formula with its returned value.

If I would want to parse all cells that contains an
occurence of my formula, how could I do it (short of
parsing every single cells and doing a mid() to find out
if there is a formula inside or not)? Is there some
active cell collection Excel gives me access to or
something like that?

Thanks,
C.

What would make you cells "active"

Set rng = Worksheets("Sheet1").Cells.Specialcells(xlFormulas )

would create a reference to cells that have formulas

if you formula returns a number then you could futher restrict it to

Set rng = Worksheets("Sheet1").Cells.SpecialCells(xlFomulas, xlNumbers)

or replace xlNumbers with xlTextValues

That at least narrows down the search.

for each cell in rng
if instr(cell.Formula,"myfunc") then
' one instance found
end if
Next

--
Regards,
Tom Ogilvy

"Conceptor" wrote in message
...
Hi,

I am trying to replace every sheet cells that contains a
occurence of a specific formula with its returned value.

If I would want to parse all cells that contains an
occurence of my formula, how could I do it (short of
parsing every single cells and doing a mid() to find out
if there is a formula inside or not)? Is there some
active cell collection Excel gives me access to or
something like that?

Thanks,
C.

Another way an probably faster would be to use

Worksheets("Sheet1").Cells.Find(What:="MyFunc",Loo kin:=xlFormulas)

this sample form help shows how to search an entire sheet:

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
-- Regards,Tom Ogilvy"Conceptor" wrote
in message ...
Hi,

I am trying to replace every sheet cells that contains a
occurence of a specific formula with its returned value.

If I would want to parse all cells that contains an
occurence of my formula, how could I do it (short of
parsing every single cells and doing a mid() to find out
if there is a formula inside or not)? Is there some
active cell collection Excel gives me access to or
something like that?

Thanks,
C.

Thanks again, tom.
C.
-----Original Message-----
Another way an probably faster would be to use

Worksheets("Sheet1").Cells.Find
(What:="MyFunc",Lookin:=xlFormulas)

this sample form help shows how to search an entire sheet:

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
-- Regards,Tom Ogilvy"Conceptor"
wrote
in message ...
Hi,

I am trying to replace every sheet cells that
contains a
occurence of a specific formula with its returned value.

If I would want to parse all cells that contains an
occurence of my formula, how could I do it (short of
parsing every single cells and doing a mid() to find out
if there is a formula inside or not)? Is there some
active cell collection Excel gives me access to or
something like that?

Thanks,
C.


.