Using excel #NA error to control macro

[petert]

I have a macro that expands a wooksheet by copying formula into the next column (B). Then (after some other steps) it then pastes the same formula into the next column (C), then D, E etc. Eventually the formula will return #N/A error in excel. At that point I need to stop the process. I can't make the macro consistantly respond to the excel #N/A error. It seems to work on a small test file. It fails on the data, possibly because there are other #N/A errors on the spreadsheet. The other #N/A errors are expected. Maybe it would be easier if I made excel check using ISERROR but I rather do it in the macro.

 

[Gates Is Antichrist]

Is the #N/A cell .value not "Error 2042" ? Try checking .Value.

 

[jindon]

petert

You can count the error value(s) like this

Columns("c").SpecialCells(xlFormulas, xlErrors).Count

However you need an error trap since if there is no such cell, it raise a runtime error.