Testing for #NA in VB

Posted by stevie windows on August 31, 2001 5:27 AM

I've got a macro that works through some data and I want it to insert a new row when it comes across the #NA error message. The ISNA function isn't defined in vb, and the Iserror function doesn't appear to work, any ideas? Here's the macro as it stands..

Sub NA()
Dim currentcell As Range
Set currentcell = Application.ActiveCell
Do While IsEmpty(ActiveCell) = False
If IsError(currentcell) Then
Selection.EntireRow.Insert
currentcell.Offset(1, 0).Range("A1").Select
Else
currentcell.Offset(1, 0).Range("A1").Select
End If
Loop
End Sub

Posted by Dax on August 31, 2001 6:02 AM

How about this? It will also run much quicker than your current code because it avoids selecting anything.

Sub NA()
Dim rngeCheck As Range
Set rngeCheck = ActiveCell.Resize(ActiveSheet.UsedRange.Rows.Count, 1)
rngeCheck.Select
rngeCheck.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Insert
End Sub

Hope it helps,
Dax.

Posted by stevie windows on August 31, 2001 6:09 AM

Smashing Cheers.