Expert: Tom Ogilvy - 7/10/2009

Question
Tom

I need to test each formula in a spreadsheet.  If the formula contains a reference like "Text!A" (followed by the remainder of the cell reference - there will be hundreds) then go to next cell and test.  If cell contains formula that does not make reference to "Text!A", then copy, pastespecial, value for that cell.  Process next cell.

The reference to text could be as simple as "=Text!A45" or more complex like "=IF(AC126=Up,Text!$A$55,IF(AC126=Same,Text!$A$56,Text!$A$57))"
Many thanks


Answer
Brian,

so for every cell that has a formula that does not have a reference to text, then replace that formula with the value returned

select the cells to process and run the macro

sub replaceformula()
dim cell as range
for each cell in selection
if cell.hasformula then
 if instr(1,cell.formula,"text!",vbtextcompare) = 0 then
      cell.formula = cell.value
 end if
end if
Next
End sub

test this on a copy of your data to insure it is doing what you want.

--
Regards,
Tom Ogilvy