Excel 97 macro error

[pscornelison]

I have an Excel 97 application with several macros and all seem to run fine in Excel 2007 except for one error that I don't seem to be able to resolve.

The code is

Please Login or Register  to view this content.

Very rarely, it seems to work but most times the macro fails on the second line of the above three lines of code.

Thanks.......Steve

[mrice]

Try

Please Login or Register  to view this content.

[shg]

SpeciaCells will error if there are no cells of the designated type within the range, which you can work around.

In your code you are selecting a worksheet, and then immediately applying the SpecialCells method without explicitly designating the cells to apply it to. What are you trying to do?

[pscornelison]

This is a project I inherited. It appears that this macro is taking a year end spreadsheet and all the amount fields to zero in order to start a new year. Based on what you said, I'm checking to see that the amount fields are formatted such that the macro will not error.

[pscornelison]

Actually, it seems that the error prone code is trying to reset just fields that have formulas

[romperstomper]

No, it is looking for cells that contain hardcoded numbers (i.e. not the result of a formula). However, as shg said, it assumes the current selection is correct. If you have only one cell selected, it will search the whole sheet, but if you have more than one cell selected, it will search only the selected cells.

[pscornelison]

I was trying to get this macro code working in 2007 and then upgrade to 2010. The 2010 upgrade is desirable but not absolutely necessary. But I tried opening the spreadsheet in 2010 and Excel displayed this message:

This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.

Not sure how to proceed. Maybe just forget the 2010 upgrade

[romperstomper]

Did you install VBA when you installed 2010? If not, that would happen.

[pscornelison]

Thanks for your answer. I opened Excel 2010, went to File/Options/Add-ins and enabled Analysis ToolPak and Analysis ToolPak - VBA. I still get the same message when trying to open the Excel 97 spreadsheet containing macros.

[pscornelison]

Thanks, I'll look at it. It seems to do a select of several cells before the search. I'll "STEP" through the code.

[romperstomper]

The Analysis Toolpak is irrelevant. I'm asking whether you installed VBA when you installed Office? If not, your code will be removed, as you indicated.

[pscornelison]

Thanks. I'll go back and re-install.

[pscornelison]

I added "Range("A1").select" to make sure only one cell was selected as romperstomper pointed out in shg's post and it works fine now. Also re-installed 2010 with VBA. Thanks to you both!

src