Updating "#Invalid entity XXXX" using VBA

[Guest]

"I working with a file that has about 150 columns and 1000 rows
I using a Hyperion formula that will at point return "#invalit Entity ???
does not exist

I was to use VBA to find all cases when the entity is invalid and replace it
with 0
or if there is a simpler was I'm open to it

 

[Guest]

Dim rng as range
Dim cell as Range
set rng = Activesheet.UsedRange.specialcells(xlformulas,xlTextValues)
for each cell in rng
if instr(1,cell.value,"#Invalid",vbTextCompare) then
cell.value = 0
end if
next

 

[Guest]

Thanks Tom, will try it later