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
Highlighted
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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies