Problem with #N/A error not recognized by VBA code

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
I am new to VBA and I have a problem with the following code:

Code:
Dim Count As Integer
For Count = 1 To 100
If ActiveCell = "#N/A" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select
Next Count

The code works its way down a column and is supposed to delete any row with an "#N/A" error. However, when I run my code, it stops when it comes to the "#N/A" error and I get the following error message:
Run-time error'13': Type mismatch

I think part of the problem maybe the fact that there is an earlier part of the code (not shown) that turns all "#N/A" errors to values. It appears that my code does not recognize the "#N/A" error as an error.

I would appreciate any help on this matter.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try IsError(activecell)

When you delete the row, are you sure the same column remains selected -- err, active? I actually don't know.
 
Upvote 0
Great! Using IsError(activecell) did work! Thanks for the help. I appreciate it very much.

Just for future reference, does someone know why my original code, using the #N/A, did not work.
 
Upvote 0
You can't do comparisons on error contents - only use IS functions. Thus on sheets you often see nonsense like

a1=if( IsError({intense 50 term expression}), 0, {intense 50 term expression} )

(The answer to that, oh ye optimizers, is http://www.decisionmodels.com/optspeedi.htm )

But a most excellent solution for you and your code is at Charles Williams'
http://www.decisionmodels.com/calcsecretsh.htm
which discusses Evaluate ("it returns an error rather than raising an error") !! Woohoo! :p (y)
 
Upvote 0
Check my edit, I added content I thought of after posting.

If you want to be great, keep that (and this) website in your forefront :)
 
Upvote 0
Just as a note on this.
While I agree the Iserror() method is the correct usage, it would be possible to make you original code work with a small change.

If ActiveCell.Text = "#N/A" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select

Will evaluate the correctly.

HTH
Cal
 
Upvote 0
When you're deleting rows you want to work backward through the range and you don't generally need to select cells in order to work with them.

Perhaps:

Code:
Sub test()
For x = 100 To 0 Step -1
    If ActiveCell.Offset(x).Text = "#N/A" Then ActiveCell.Offset(x).EntireRow.Delete
Next x
End Sub

or

Code:
Sub test()
For x = 100 To 0 Step -1
    If IsError(ActiveCell.Offset(x)) Then ActiveCell.Offset(x).EntireRow.Delete
Next x
End Sub
 
Upvote 0
Cbrine & Hotpepper:
Your solutions are exactly what I was looking for originally. I knew that some minor change to my code could be done to make it work. Thanks so much for this input. I will put it to good use in the future. I appreciate your interest in helping me.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top