Replace sheet formulas with value

  • Thread starter Thread starter Conceptor
  • Start date Start date
C

Conceptor

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
 
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
 
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"
Click to expand...
 
Back
Top