Forum Discussion

andy99330's avatar
andy99330
Copper Contributor
May 07, 2019

Excel formula help

im using a vlookup process to pull data from one sheet to another but the number of lines varies daily. how do i stop the #N/A being entered into cells when there is no return, or how do i limit the ...
excel
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
May 07, 2019
You may wrap your formula with IFNA, or to be sure, IFERROR.
andy99330's avatar
andy99330
Copper Contributor
May 07, 2019

Twifoo,

thanks for the suggestion though i am a little confused how to wrap the formula with an IF type of statement. My formula is shown below

 

Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],ProdOver!C[-2]:C[2],2,FALSE)"
Selection.AutoFill Destination:=Range("C3:C150"), Type:=xlFillDefault

 

but if i wrap it in an IF statement wouldn't that conflict with the VLOOKUP? To have the IF statement return a blank cell it needs the #N/A response from the VLOOKUP but then will delete the formula - or what am i missisng?

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 07, 2019
    What you just illustrated is not a formula. It’s a VBA code, which is not my forte.

Resources