For i = 5 To 300
Set curCell = Worksheets("Sheet2").Cells(i, 4)
If curCell.Text = "#N/A" Then
lastrow = Worksheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0).Row
Worksheets("Sheet1").Range("A" & lastrow).Value = curCell
Worksheets("Sheet1").Range("B" & lastrow).Value = curCell.Offset(0, 1)
End If
Next i

"Stephen" wrote:

Hi Folks,

I have a bunch of data in columns D & E on a sheet named "Compare". Column
D is full of VLOOKUP formulas some of which result in a #N/A error. I would
like to copy and paste the contents columns D & E for any row where column
D's formula results in #N/A. My list runs from row 6 to row 300 and I only
want to return the offending #N/A rows and paste their contents from columns
D & E to columns A & B on a sheet named "Results".

Can this be done in a macro?

Thanks in Advance.

Forgot to change sheet names
For i = 5 To 300
Set curCell = Worksheets("Compare").Cells(i, 4)
If curCell.Text = "#N/A" Then
lastrow = Worksheets("Results").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0).Row
Worksheets("Results").Range("A" & lastrow).Value = curCell
Worksheets("Results").Range("B" & lastrow).Value = curCell.Offset(0,
1)
End If
Next i
"Mike" wrote:

For i = 5 To 300
Set curCell = Worksheets("Sheet2").Cells(i, 4)
If curCell.Text = "#N/A" Then
lastrow = Worksheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0).Row
Worksheets("Sheet1").Range("A" & lastrow).Value = curCell
Worksheets("Sheet1").Range("B" & lastrow).Value = curCell.Offset(0, 1)
End If
Next i

"Stephen" wrote:

Hi Folks,

I have a bunch of data in columns D & E on a sheet named "Compare". Column
D is full of VLOOKUP formulas some of which result in a #N/A error. I would
like to copy and paste the contents columns D & E for any row where column
D's formula results in #N/A. My list runs from row 6 to row 300 and I only
want to return the offending #N/A rows and paste their contents from columns
D & E to columns A & B on a sheet named "Results".

Can this be done in a macro?

Thanks in Advance.