Using excel #NA error to control macro

petert

New Member
Joined
Aug 29, 2007
Messages
1
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top