Need help with ISNA

[instanceoftime]

I would like to program a macro (actually include in current marco) to do the following.

The following code searches a master file looking for associated prices. However there is a possibility that A1 doesn't contain the number looked for.
This gives me the #NA error in the cell. I would like to go down column E and change the #NA to zero.

Sub pricing()
'
' pricing Macro
'
' Keyboard Shortcut: Ctrl+u
'


Dim lr As Long




    lr = Cells(Rows.Count, "A").End(xlUp).Row  
    
    Range("E1:E" & lr).Formula = "=VLOOKUP(A1,[final.xls]Items!$A$1:$H$40000,8,0)"[COLOR=#ff0000] 'this fetches price from a different workbook and places value in column E in the current workbook[/COLOR]


    [COLOR=#ff0000]'I want to go down column E and change [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA]#NA[/URL]  to zero[/COLOR]


End Sub

 

[Atroxell]

I would like to program a macro (actually include in current marco) to do the following.

The following code searches a master file looking for associated prices. However there is a possibility that A1 doesn't contain the number looked for.
This gives me the #NA error in the cell. I would like to go down column E and change the #NA to zero.

Sub pricing()
'
' pricing Macro
'
' Keyboard Shortcut: Ctrl+u
'


Dim lr As Long




    lr = Cells(Rows.Count, "A").End(xlUp).Row  
    
    Range("E1:E" & lr).Formula = "=VLOOKUP(A1,[final.xls]Items!$A$1:$H$40000,8,0)"[COLOR=#ff0000] 'this fetches price from a different workbook and places value in column E in the current workbook[/COLOR]


    [COLOR=#ff0000]'I want to go down column E and change [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA"]#NA[/URL]  to zero[/COLOR]


End Sub

Why don't you just use "IFERROR"? (See mod in blue.)

Range("E1:E" & lr).Formula = "=[COLOR=#0000ff]IFERROR([/COLOR]VLOOKUP(A1,[final.xls]Items!$A$1:$H$40000,8,0)[COLOR=#0000ff],0)[/COLOR]"[COLOR=#ff0000] 'this fetches price from a different workbook and places value in column E in the current workbook
[/COLOR]

HTH

 

[instanceoftime]

awesome, I was looking into how to wrap it in IFERROR as well but both are beyond me. Thanks .... Your solution worked perfect!