Copying data using key column and excluding #N/A

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?
MamarazziAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Using Excel 2003...



Sub Copy_Formula()
    With Selection
        .ClearContents 
        .FormulaR1C1 = _
            "=IF(ISNA(VLOOKUP(RC[-7],[Book2.xls]Sheet1!R5C3:R181C4,2,FALSE)),"""",VLOOKUP(RC[-7],[Book2.xls]Sheet1!R5C3:R181C4,2,FALSE))" 
        .Value = .Value
    End With
End Sub

Open in new window


In Excel 2007 and later:

Sub Copy_Formula()
    With Selection
        .ClearContents 
        .FormulaR1C1 = _
            "=IFERROR(VLOOKUP(RC[-7],[Book2.xls]Sheet1!R5C3:R181C4,2,FALSE),"""")" 
        .Value = .Value
    End With
End Sub

Open in new window

0
 
MamarazziAuthor Commented:
Fabolous! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.