Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-542937%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542937%22%20slang%3D%22en-US%22%3E%3CP%3Eim%20using%20a%20vlookup%20process%20to%20pull%20data%20from%20one%20sheet%20to%20another%20but%20the%20number%20of%20lines%20varies%20daily.%20how%20do%20i%20stop%20the%20%23N%2FA%20being%20entered%20into%20cells%20when%20there%20is%20no%20return%2C%20or%20how%20do%20i%20limit%20the%20search%20to%20only%20cells%20with%20data%20in%20them%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-542937%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543332%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543332%22%20slang%3D%22en-US%22%3EYou%20may%20wrap%20your%20formula%20with%20IFNA%2C%20or%20to%20be%20sure%2C%20IFERROR.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543895%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%3C%2FP%3E%3CP%3Ethanks%20for%20the%20suggestion%20though%20i%20am%20a%20little%20confused%20how%20to%20wrap%20the%20formula%20with%20an%20IF%20type%20of%20statement.%20My%20formula%20is%20shown%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22C3%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22%3DVLOOKUP(RC%5B-1%5D%2CProdOver!C%5B-2%5D%3AC%5B2%5D%2C2%2CFALSE)%22%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22C3%3AC150%22)%2C%20Type%3A%3DxlFillDefault%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20if%20i%20wrap%20it%20in%20an%20IF%20statement%20wouldn't%20that%20conflict%20with%20the%20VLOOKUP%3F%20To%20have%20the%20IF%20statement%20return%20a%20blank%20cell%20it%20needs%20the%20%23N%2FA%20response%20from%20the%20VLOOKUP%20but%20then%20will%20delete%20the%20formula%20-%20or%20what%20am%20i%20missisng%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543988%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543988%22%20slang%3D%22en-US%22%3EWhat%20you%20just%20illustrated%20is%20not%20a%20formula.%20It%E2%80%99s%20a%20VBA%20code%2C%20which%20is%20not%20my%20forte.%3C%2FLINGO-BODY%3E
andy99330
New 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?

Highlighted
What you just illustrated is not a formula. It’s a VBA code, which is not my forte.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies