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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Try IsError(activecell)

When you delete the row, are you sure the same column remains selected -- err, active? I actually don't know.
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
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.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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)
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112

ADVERTISEMENT

Thanks for that insight. Being very new to VBA, I did not know that.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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 :)
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112

ADVERTISEMENT

Thanks for the additional input. I'll check it out.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
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.
 

Forum statistics

Threads
1,136,261
Messages
5,674,705
Members
419,520
Latest member
Jennifer4Dillon

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