<div>
<div class="content wysiwyg-content">
I want to copy data from one workbook to another using a key column that exist in both.<br />
<br />
So far I've tried using VLOOKUP but I'm not sure it is the best way.<br />
<br />
I have a button that runs a macro. First I select the cells I want the formula in:<br />
<br />
Sub Copy_Formula()<br />
&nbsp; &nbsp; Selection.ClearContents <br />
&nbsp; &nbsp; Selection.FormulaR1C1 = _<br />
&nbsp; &nbsp; &nbsp; &nbsp; &quot;=VLOOKUP(RC[-7],[Book2.xl<wbr />s]Sheet1!R<wbr />5C3:R181C4<wbr />,2,FALSE)&quot;<wbr /> <br />
End Sub<br />
<br />
This works fine, BUT... I want the values in the cells, not the formula itself. And I want to clear the #N/A that I get in the cells if the formula has no match.<br />
<br />
Any idea?
</div>
</div>


I want to copy data from one workbook to another using a key column that exist in both.

So far I've tried using VLOOKUP but I'm not sure it is the best way.

I have a button that runs a macro. First I select the cells I want the formula in:

Sub Copy_Formula()
    Selection.ClearContents 
    Selection.FormulaR1C1 = _
        "=VLOOKUP(RC[-7],[Book2.xls]Sheet1!R5C3:R181C4,2,FALSE)" 
End Sub

This works fine, BUT... I want the values in the cells, not the formula itself. And I want to clear the #N/A that I get in the cells if the formula has no match.

Any idea?

Copying data using key column and excluding #N/A

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.