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.

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.

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.

Stephen,

You didn't actually tell me what I was comparing in columns G&A so this
copies them if they are the same

Sub Not_To_Be_A_Pest()
Dim MyRange, MyRange1 As Range
Sheets("Compare").Select
lastrow = Sheets("Compare").Cells(Rows.Count, "G").End(xlUp).Row
Set MyRange = Sheets("Compare").Range("G1:G" & lastrow)
For Each c In MyRange
If c.Value = c.Offset(0, -6).Value 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("D1").Select
ActiveSheet.PasteSpecial
End If
End Sub


Mike

"Stephen" wrote:

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.

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.

My appologies, I'm not making myself clear.

Actually it's two seperate problems I'm trying to tackle. From the first
list in Columns D & E I'm wanting to return all #N/A values. From the second
list in columns G & H, I want to compare values in column H against values in
column D and return the values from (rows) from columns G & H where the
column H vlue does not appear in the column D values.



"Mike H" wrote:

Stephen,

You didn't actually tell me what I was comparing in columns G&A so this
copies them if they are the same

Sub Not_To_Be_A_Pest()
Dim MyRange, MyRange1 As Range
Sheets("Compare").Select
lastrow = Sheets("Compare").Cells(Rows.Count, "G").End(xlUp).Row
Set MyRange = Sheets("Compare").Range("G1:G" & lastrow)
For Each c In MyRange
If c.Value = c.Offset(0, -6).Value 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("D1").Select
ActiveSheet.PasteSpecial
End If
End Sub


Mike

"Stephen" wrote:

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.