Why does my IFNA (or ISNA) not working in a VBA module?

i have the following code:

Sub TestNA2()
IsThisNA = Sheets("sheet1").Range("D:D").Select
'Sheets("sheet1").Range("E:E").Select
Range("E2").Formula = "=IF(ISNA(D1),""Delete"","""")"
Range("E2").AutoFill Destination:=Range("E:E"), Type:=xlFillDefault
'ActiveCell.Value = "=IF(ISNA(D:D), ""Delete"","""")"
'ActiveCell.Value = IsThisNA
'MsgBox IsNA
End Sub

but it errors out on this line: ActiveCell.Value = IfNa(D6, "Delete")

what i want to happen is to put the word "delete" in cell E6 if D6 is #NA.

can someone help me out with this code?

2 Answers

The error is in this row:

ActiveCell.Value = IfNa(D6, "Delete")

Use instead:

Set IsThisNA = Sheets("items-1").Range("D6")
If Application.WorksheetFunction.IsNA(IsThisNA.Value) Then ActiveCell.Value = "Delete"
End If

or, more succinctly:

If Application.WorksheetFunction.IsNA(Sheets("items-1").Range("D6").Value) Then ActiveCell.Value = "Delete"
End If

You should make a string out of the formula. You have:

ActiveCell.Value = IfNa(D6, "Delete")

But this should be:

ActiveCell.Formula = "=IF(ISNA(D6), ""Delete"","""")"

Note that I added the second parameter of IF to be the empty string, but you might want to change this.

Note: you can use IFNA since Excel 2011 instead of IF(ISNA(..),..).

Edit: based on this answer you can extend it for the whole column:

Range("E1").Formula = "=IF(ISNA(D1),""Delete"","""")"
Range("E1").AutoFill Destination:=Range("E:E"), Type:=xlFillDefault

You can also put a fixed range, for example E1:E6, as indicated in here.

Edit 2: if you want to fill it for the whole column, you should put the formula on row 1 (e.g. E1), and do the auto fill over the whole column (e.g. E:E). If you want less rows you should indicate that in the auto fill destination, for example:

Range("E2").Formula = "=IF(ISNA(D6),""Delete"","""")"
Range("E2").AutoFill Destination:=Range("E2:E10"), Type:=xlFillDefault

So the auto fill range is starting at the same location as the formula (E2 in this example).