Excel formula help

Copper Contributor

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 search to only cells with data in them?

 

3 Replies
You may wrap your formula with IFNA, or to be sure, IFERROR.

@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?

What you just illustrated is not a formula. It’s a VBA code, which is not my forte.