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

 

excel
Formulas and Functions

3 Replies

  • 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