Styjun

Stop email from sending using AMPscript

Lípínguapua dopo Pêpê

Is there any way I will not use l'Hôpital's rule here?

What's the relationship betweeen MS-DOS and XENIX?

Why won't the Republicans use a superdelegate system like the DNC in their nomination process?

onomatopoeia for cluelessness

Why do my bicycle brakes get worse and feel more 'squishy" over time?

How can God warn people of the upcoming rapture without disrupting society?

What is the hottest thing in the universe?

Did Pope Urban II issue the papal bull "terra nullius" in 1095?

How to proceed further in this Arithmetico-Geometric Progression problem

Help, I cannot decide when to start the story

Output the list of musical notes

Bringing Power Supplies on Plane?

What should we do with manuals from the 80s?

Weird resistor with dots around it

Why aren't rockets built with truss structures inside their fuel & oxidizer tanks to increase structural strength?

What would it take to get a message to another star?

Solving pricing problem heuristically in column generation algorithm for VRP

Is there a word for returning to unpreparedness?

Unconventional examples of mathematical modelling

Do I have to cite common CS algorithms?

What is the opposite of "hunger level"?

How to prevent criminal gangs from making/buying guns?

Delete N/A when using VLOOKUP

Return empty cell from formula in Excelusing hyperlink in a vlookup functionif error and vlookup in vba macrosExcel VLOOKUP between two sheets failingcopy data from each cell in range loop and paste it on another sheetUsing nesting vlookup formulas for use in automation codeUsing Vlookup to copy and paste data into a separate worksheet using VBAVLOOKUP for different sheetsVBA IFERROR to VLOOKUP referencing another sheetCopy values to a sheet containing formulas without formulas being removed

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I'm trying to bring the values from Sheet2 to Sheet1 using VLOOKUP formula (in VBA, after using the formula I copy paste in order to just see the value). But when the cells from Sheet2 are empty I get the "#N/A", which I don't want. What can I do to get the cell empty if there isn't any value, like replace the #N/A with nothing.

I have tried using the IFERROR formula, but I get 0 instead of nothing.

comm.Range(.Cells(2, "C"), .Cells(lr, "C")).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet2!C[-2]:C[22],3,0),"""")"

excel vba excel-formula

share|improve this question

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Works fine for me. You will get a 0 if the cell has a 0 else you will get a "blank value"
  
  – Siddharth Rout
  Mar 27 at 11:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What happens if you actually try that formula directly in a cell?
  
  – Zac
  Mar 27 at 11:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Zac if works by using it directly in the cell, but I cannot. The client uses an userform to complete the empty data after the VLOOKUP, this is why I used it in VBA, because after I copy paste it to remove the formula
  
  – Anca Vulc
  Mar 27 at 12:02
  

  
  
  
  

add a comment | 

1

I'm trying to bring the values from Sheet2 to Sheet1 using VLOOKUP formula (in VBA, after using the formula I copy paste in order to just see the value). But when the cells from Sheet2 are empty I get the "#N/A", which I don't want. What can I do to get the cell empty if there isn't any value, like replace the #N/A with nothing.

I have tried using the IFERROR formula, but I get 0 instead of nothing.

comm.Range(.Cells(2, "C"), .Cells(lr, "C")).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet2!C[-2]:C[22],3,0),"""")"

excel vba excel-formula

share|improve this question

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Works fine for me. You will get a 0 if the cell has a 0 else you will get a "blank value"
  
  – Siddharth Rout
  Mar 27 at 11:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What happens if you actually try that formula directly in a cell?
  
  – Zac
  Mar 27 at 11:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Zac if works by using it directly in the cell, but I cannot. The client uses an userform to complete the empty data after the VLOOKUP, this is why I used it in VBA, because after I copy paste it to remove the formula
  
  – Anca Vulc
  Mar 27 at 12:02
  

  
  
  
  

add a comment | 

I'm trying to bring the values from Sheet2 to Sheet1 using VLOOKUP formula (in VBA, after using the formula I copy paste in order to just see the value). But when the cells from Sheet2 are empty I get the "#N/A", which I don't want. What can I do to get the cell empty if there isn't any value, like replace the #N/A with nothing.

I have tried using the IFERROR formula, but I get 0 instead of nothing.

comm.Range(.Cells(2, "C"), .Cells(lr, "C")).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet2!C[-2]:C[22],3,0),"""")"

excel vba excel-formula

share|improve this question

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

comm.Range(.Cells(2, "C"), .Cells(lr, "C")).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet2!C[-2]:C[22],3,0),"""")"

share|improve this question

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

share|improve this question

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

edited Mar 27 at 12:58

Pᴇʜ

31.3k66 gold badges3030 silver badges5656 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

asked Mar 27 at 11:39

Anca VulcAnca Vulc

6766 bronze badges

1 Answer
1

active

oldest

votes

1

• You could use =IFNA instead of =IFERROR because the code in case of error generate #N/A error.


• Lastly, in your formula after the number of column you want to return (in your case column number 3) you use number 0 instead of TRUE or FALSE.

The below code is not the answer but a sample of the formula.

Code Sample:

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA

share|improve this answer

edited Mar 27 at 11:58

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In excel, TRUE and FALSE are treated as 1 and 0 respectively. I use 1 and 0 for the true false portion VLOOKUP/HLOOKUP all the time without issue.
  
  – Forward Ed
  Mar 27 at 12:05
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55376310%2fdelete-n-a-when-using-vlookup%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

• You could use =IFNA instead of =IFERROR because the code in case of error generate #N/A error.


• Lastly, in your formula after the number of column you want to return (in your case column number 3) you use number 0 instead of TRUE or FALSE.

The below code is not the answer but a sample of the formula.

Code Sample:

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA

share|improve this answer

edited Mar 27 at 11:58

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In excel, TRUE and FALSE are treated as 1 and 0 respectively. I use 1 and 0 for the true false portion VLOOKUP/HLOOKUP all the time without issue.
  
  – Forward Ed
  Mar 27 at 12:05
  

  
  
  
  

add a comment | 

1

• You could use =IFNA instead of =IFERROR because the code in case of error generate #N/A error.


• Lastly, in your formula after the number of column you want to return (in your case column number 3) you use number 0 instead of TRUE or FALSE.

The below code is not the answer but a sample of the formula.

Code Sample:

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA

share|improve this answer

edited Mar 27 at 11:58

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In excel, TRUE and FALSE are treated as 1 and 0 respectively. I use 1 and 0 for the true false portion VLOOKUP/HLOOKUP all the time without issue.
  
  – Forward Ed
  Mar 27 at 12:05
  

  
  
  
  

add a comment | 

• You could use =IFNA instead of =IFERROR because the code in case of error generate #N/A error.


• Lastly, in your formula after the number of column you want to return (in your case column number 3) you use number 0 instead of TRUE or FALSE.

The below code is not the answer but a sample of the formula.

Code Sample:

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA

share|improve this answer

edited Mar 27 at 11:58

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA

share|improve this answer

edited Mar 27 at 11:58

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges

answered Mar 27 at 11:53

Error 1004Error 1004

5,35522 gold badges88 silver badges2525 bronze badges