Need help with ISNA

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
69
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.

Code:
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

Active Member
Joined
Apr 18, 2007
Messages
417
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.

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

Code:
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

Board Regular
Joined
Mar 23, 2011
Messages
69
awesome, I was looking into how to wrap it in IFERROR as well but both are beyond me. Thanks .... Your solution worked perfect!
 

Forum statistics

Threads
1,085,543
Messages
5,384,343
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top