Using the function ISNA in a macro

Hi,

Hoping someone will be able to help me out.

I've got a macro which basically reformats data from another source.

It then places all the data on a new sheet. My problem is that some of the formula that the macro places on the new sheet come back with the result #N/A - which is fine, as they are the result of a lookup.

What I want to try and do, is at the end of the macro, get it to delete the row if the activecell is showing as #N/A

I've tried to write a small bit of code for it...

Do
If ActiveCell.Value = "ISNA"
Rows(ActiveCell.Row).Columns("A:M").Select
Selection.Delete
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Loop While ActiveCell.Value <> "0"

The code does not seem to like 'ISNA' or '#N/A'. Any ideas on how I can get past this?

Cheers

Please Login or Register  to view this content.

Be aware when using the .Text property that columns that are not wide enough will display ###'s and the test will return False.

For anyone that might want to use this code, this is what I have at the end of it.

I've had to change the code slightly as

Loop While ActiveCell.Value <> "0"

did not work. The #N/A was returning a value of 'Error 2404' or something like that, so = 0 was invalid.

To address this I used this extra bit of code

Sheets("Sheet").Select
Range("B3").Select
l = Cells(Rows.Count, "B").End(xlUp).Row 'I've used this to find the length of the data (in rows)
Range("A3").Select 'In column A I have a formula, so I've used this as the data to look at to see when the data has reached the bottom as this will always have a value unless there is no data in column B
Selection.Copy
Range("A3:A" & l).Select 'This shows the macro where to copy the formula down to, using 'I', defined above as the ending cell in the range
ActiveSheet.Paste
Calculate

Range("B3").Select

Do
If Application.WorksheetFunction.IsNA(ActiveCell) Then
Rows(ActiveCell.Row).Columns("A:M").Select
Selection.Delete
ActiveCell.Offset(0, 1).Select ' This bit was also changed as having it as 'ActiveCell.Offset(1, 1).Select' meant that it would skip a row when an #N/A row was found and deleted
Else
ActiveCell.Offset(1, 0).Select
End If

Loop While ActiveCell.Offset(0, -1) <> "" 'This now looks at the forumla in column A, instead of the value in B to know when to stop the loop

Range("B3").Select

This may save you doing all the selecting of cells.

Please Login or Register  to view this content.