My VBA code does not work in Office 2007

[hamohd70]

I developed and tested some VBA code using Office 2013 and was working perfectly. BUT when I ran it on Office 2007, the code breaks at this line...

ActiveSheet.Range("D6").Value = "=" & Worksheets("Formula").Range("AlarmsStatusFormula").Value

the error msg I received was Run-time error 1004: Application-defined or object-defined error.

Can you please help with this?

 

[RoryA]

What is in the AlarmsStatusFormula cell? Does it involve any functions introduced after 2007 perhaps?

 

[nemmi69]

Was this saved in an Excel version later than 2007? If so then an add-in for the later version will be missing from 2007.

 

[hamohd70]

What is in the AlarmsStatusFormula cell? Does it involve any functions introduced after 2007 perhaps?

AlarmStatusFormula contains this formula

IF(OR(ISNUMBER(SEARCH({"Recover"," NR"},[Message]))),"FOUND","")

 

[hamohd70]

Was this saved in an Excel version later than 2007? If so then an add-in for the later version will be missing from 2007.

yes. this code was written and saved in Office 2013.

 

[RoryA]

Is D6 part of a table that includes a column called Message?

 

[hamohd70]

Is D6 part of a table that includes a column called Message?

Yes. D6 is part of the same table that contains a column called "Message".

the strange thing here is that the code works fine on Office 2013!

 

[RoryA]

That should really be an array formula though, unless you meant to refer to that column in the same row?

 

[hamohd70]

That should really be an array formula though, unless you meant to refer to that column in the same row?

It is an array formula. what I'm try to do is looking for "Recover" or " NR" in the column "Message" and then update D6 with "Found" or just leave it empty.

The named range "AlarmsStatusFormula" has the formula that will do the work for me.

Can it be something to do with the way Excel 2007 handles named ranges vs 2013?