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

Example of XML tab

VBAMicrosoft ExcelMicrosoft Office

Last Comment

Koen

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Norie

4/12/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Koen

4/12/2018

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...

KANEDA 0149

4/12/2018

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))

KANEDA 0149

4/12/2018

ASKER

Thanks Norie, that additional line item in your code worked exactly as I needed it!

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Koen

4/12/2018

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