ExcelBanter - Formula in code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Formula in code (https://www.excelbanter.com/excel-programming/393292-formula-code.html)

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1

Please show your formula.

"jln via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1

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

--
Regards,
Tom Ogilvy

"jln via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1