Wow, pretty good stuff. Worked like a charm.

Not to be a pest but do you have one that will compare values in Column "G"
on the "Compare" sheet, with the values in Column "A" on the "Compare" sheet,
and return the row values from Columns G & H to the "Results" sheet in
columns D & E?

"Mike H" wrote:

Stephen,

Alt+F11 to open VB editor. Right click 'This Worbook' and insert module.
Paste this in and try it

Sub sidence()
Dim MyRange, MyRange1 As Range
Sheets("Compare").Select
lastrow = Sheets("Compare").Cells(Rows.Count, "D").End(xlUp).Row
Set MyRange = Sheets("Compare").Range("D1:D" & lastrow)
For Each c In MyRange
If c.Text = "#N/A" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.Resize(, 2)
Else
Set MyRange1 = Union(MyRange1, c.Resize(, 2))
End If
End If
Next

If Not MyRange1 Is Nothing Then
MyRange1.Copy
Sheets("results").Select
Range("A1").Select
ActiveSheet.Paste
End If
End Sub

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