Where to put IF(ISNA...

[plank]

Hello,

Can someone help with the following please?:

Im trying to use the IF(ISNA(.. function in this piece of code

Sub Refresh()
    
    With Sheets("Final").Range("B5:N20")
        .Formula = "=IF(OR($A5="""",B$4=""""),"""",VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0))"
        .Value = .Value
    End With
    
End Sub

Im not sure where its supposed to go, as everytime I use it I get the "Runtime error 1004". I would like it to say "Not in data" when no data is available.

Also, in the workbook attached, if you hit the refresh button and there is no value for a certain field in the "Data" sheet, it sets the default to "0", is there a way to leave this blank if there's no value?

Thanks in advance

[plank]

Bump......

[NBVC]

Does this fix it?

Sub Refresh()
    On Error Resume Next
    With Sheets("Final").Range("B5:N20")
        .Formula = "=IF(ISNA(MATCH(B$4,Data!$A$1:$A$1,0)),"""",VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0))"
        .Value = .Value
    End With
    
End Sub

[plank]

Hello,

Thanks for your response, this doesnt fix it, it clears all the data =/

[Cutter]

Perhaps:

"=IF(OR($A5="""",B$4=""""),"""",IF(ISNA(VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0),"""",VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0))))"

[NBVC]

How about?

Sub Refresh()
    On Error Resume Next
    With Sheets("Final").Range("B5:N20")
        .Formula = "=IF(OR($A5="""",B$4=""""),"""",IF(ISNA(VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0)),"""",IF(VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0)=0,"""",VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0))))"
        .Value = .Value
    End With
    
End Sub

[plank]

Hello,

Thank you both for your help, its no longer showing "#NA". If I wanted to show "Not in data" what should i do?

I think it looks like this, but I keep getting an error:

IF(ISNA(VLOOKUP(....)),"Not in data")

[NBVC]

You have to maintain doubling of double-quotes:

Sub Refresh()

...
   
        .Formula = "=IF(OR($A5="""",B$4=""""),"""",IF(ISNA(VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0)),""No Data"",IF(VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0)=0,"""",VLOOKUP($A5,Data!$A$2:$I$9,MATCH(B$4,Data!$A$1:$I$1,0),0))))"
       
...
    End With
    
End Sub

[plank]

Thanks so much NBVC!!!

[Cutter]

Thank you both for your help

You're welcome. Thanks for the 'star tap'.