Find Cells Containing #N/A & Copy

  • Hi all,

    Im currently making a macro that vlookups similar information from one sheet into the other but i was wondering if there was a way to create a vb code for the macro that would allow it note cells that have come up as '#N/A' and paste that cell and an accompanying cell into a separate section of the sheet?

    thanks for any help

  • Re: Macro Vba Coding


    If you are actually using the Excel Vlookup function in the VBA code then

    MyCell = Application.WorksheetFunction.VLookup(MyData, MyTable, 2, False)
    If MyCell = "#N/A" Then MySheet.MyCell = "MyCell = #N/A"

    IF you are using the VBA Find method in the VBA code (recommended) then

    Set MyRange = MyTable.Find(MyData, LookIn:=xlValues, LookIn:=xlWhole)
    If MyRange Is Nothing Then MySheet.MyCell = "MyCell = Nothing"

    BTW Mike, You should change your thread title to something more to the subject. Every body is asking about "Macro Vba coding"

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!