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).
