Link to home
Start Free TrialLog in
Avatar of KANEDA 0149
KANEDA 0149Flag for United States of America

asked on

VBA Code to Ignore Skip Copy/Paste Command on Cells Containing #N/A

I have an Excel workbook with a macro to copy/paste specific cells from 'Sheet1' worksheet into specific cells to 'XML' worksheet.  The code works great but for cells in 'Sheet1' which the formula is returning #N/A (as expected), I would like the VBA script that I currently have to skip or ignore those cell ranges (however many it may be).  Any assistance would be great!

VBA Code
Sub Copy()
Dim LR As Long, i As Long, j As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        j = j + 1
        .Range("M" & i & ":M" & i).Copy
        Sheets("XML").Range("A" & j).PasteSpecial Paste:=xlPasteValues
        .Range("A" & i & ":A" & i).Copy
        Sheets("XML").Range("C" & j).PasteSpecial Paste:=xlPasteValues
        .Range("N" & i & ":P" & i).Copy
        Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
        .Range("Q" & i & ":Q" & i).Copy
        Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
    
        j = j + 1
        .Range("D" & i & ":D" & i).Copy
        Sheets("XML").Range("B" & j).PasteSpecial Paste:=xlPasteValues
        .Range("R" & i & ":R" & i).Copy
        Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
        .Range("C" & i & ":C" & i).Copy
        Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
        .Range("B" & i & ":B" & i).Copy
        Sheets("XML").Range("J" & j).PasteSpecial Paste:=xlPasteValues

    Next i
End With
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

End Sub

Open in new window


Example of Sheet1 tab
User generated image
Example of XML tab
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or alternatively you could add  an iferror to the formula on sheet1, turning the cell into a blank, hence ending the range...
=iferror(someformula,"")

you wouldn't need to change your code then...
however will not work if you have rows of #N/A in the midst of your data...
Avatar of KANEDA 0149

ASKER

@Norie it would be columns A thru columns R, in addition to any rows where #N/A exist.   The rows can go down up to 8,000 for example.

@Koen, I did try that already but the code still copied the empty cells in columns A thru columns R treating them as "active" cells creating blank active rows and columns.

Column A contains an array formula, like so =INDEX('Fee Recon Detail'!$A$2:$A$4,MATCH(0,COUNTIF($A$5:A8,'Fee Recon Detail'!$A$2:$A$4),0))

Columns B thru R contains a formula like so =IF(A9="","",SUMIF('Fee Recon Detail'!A:A,A9,'Fee Recon Detail'!B:B))
Thanks Norie, that additional line item in your code worked exactly as I needed it!
of course, my bad... you do a row count, and the formulas are counted.

you could loop with a Do While AXX <>"", but that would also mean changing the code, would still not deal with #N/A in the middle (if existing), so then I'd follow Norie