Formula in code

[jln via OfficeKB.com]

OK i have a formula that pulls from two different sheets. If one of the
sheets is not there I get the #REF! error which i get why. Where im stuck is
which ever sheet is not there i need to adjust the formula so that it will
then only pull from the one sheet. I just cant think of how to do this in
code.

 

[Guest]

Please show your formula.

 

[Guest]

Dim cell as Range

if cell.Text = "#REF!" then
s = cell.Formula
if instr(1,cell.formula,"Sheet1",vbTextCompare) then
s = Replace(s,"Sheet1","Sheet2")
else
s = Replace(s,"Sheet2","Sheet1")
end if
cell.Formula = s
End if