Hi Folks,

I have a sheet named "Compare" with data in columns D, E, G, & H. I would
like to perform a search of the values in Column H begining at row 2 to end,
against the values in column D beginning on row 6 to end, and return row
values from columns G & H where the H value for a given row DOES NOT appear
in column D. I need the returned values to be listed on a second sheet named
"Results" in columns D & E beginning on row 2.

For example if "Compare" H2 has a value of 0062 and that value does not
appear anywhere in "Compare" column D, then copy and paste the values from
Compare" G2 & H2 to "Results" D2 & E2. Then start again with "Compare" H3
against "Compare D and if there is no match in column D, then copy and paste
"Compare" G3 & H3 to "Results D3 & E3.

I'm only looking to copy and paste those rows from "Compare" columns G & H
where there is no match. If there is a match then do nothing and move on to
the next H row on "Compare".

Any help is always greatly appreciated.

TIA!

Sub no_match()

ResultsRowcount = 2
CompareRowcount = 2
With Sheets("Compare")
Lastrow = .Range("D" & Rows.Count).End(xlUp).Row
Set D_Range = .Range("D6:D" & Lastrow)
Do While .Range("H" & CompareRowcount) < ""
G_Data = .Range("G" & CompareRowcount)
H_Data = .Range("H" & CompareRowcount)
Set c = D_Range.Find(what:=H_Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Sheets("Results")
.Range("D" & ResultsRowcount) = G_Data
.Range("E" & ResultsRowcount) = H_Data
ResultsRowcount = ResultsRowcount + 1
End With
End If
CompareRowcount = CompareRowcount + 1
Loop
End With

End Sub

"Stephen" wrote:

Hi Folks,

I have a sheet named "Compare" with data in columns D, E, G, & H. I would
like to perform a search of the values in Column H begining at row 2 to end,
against the values in column D beginning on row 6 to end, and return row
values from columns G & H where the H value for a given row DOES NOT appear
in column D. I need the returned values to be listed on a second sheet named
"Results" in columns D & E beginning on row 2.

For example if "Compare" H2 has a value of 0062 and that value does not
appear anywhere in "Compare" column D, then copy and paste the values from
Compare" G2 & H2 to "Results" D2 & E2. Then start again with "Compare" H3
against "Compare D and if there is no match in column D, then copy and paste
"Compare" G3 & H3 to "Results D3 & E3.

I'm only looking to copy and paste those rows from "Compare" columns G & H
where there is no match. If there is a match then do nothing and move on to
the next H row on "Compare".

Any help is always greatly appreciated.

TIA!

Brilliant! That's exactly what I was looking for.

I've just gotta say that some of you guys have a fantastic skill and I, for
one, truly appreciate that you are willing to share your knowledge with the
rest of us.

"Joel" wrote:


Sub no_match()

ResultsRowcount = 2
CompareRowcount = 2
With Sheets("Compare")
Lastrow = .Range("D" & Rows.Count).End(xlUp).Row
Set D_Range = .Range("D6:D" & Lastrow)
Do While .Range("H" & CompareRowcount) < ""
G_Data = .Range("G" & CompareRowcount)
H_Data = .Range("H" & CompareRowcount)
Set c = D_Range.Find(what:=H_Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Sheets("Results")
.Range("D" & ResultsRowcount) = G_Data
.Range("E" & ResultsRowcount) = H_Data
ResultsRowcount = ResultsRowcount + 1
End With
End If
CompareRowcount = CompareRowcount + 1
Loop
End With

End Sub

"Stephen" wrote:

Hi Folks,

I have a sheet named "Compare" with data in columns D, E, G, & H. I would
like to perform a search of the values in Column H begining at row 2 to end,
against the values in column D beginning on row 6 to end, and return row
values from columns G & H where the H value for a given row DOES NOT appear
in column D. I need the returned values to be listed on a second sheet named
"Results" in columns D & E beginning on row 2.

For example if "Compare" H2 has a value of 0062 and that value does not
appear anywhere in "Compare" column D, then copy and paste the values from
Compare" G2 & H2 to "Results" D2 & E2. Then start again with "Compare" H3
against "Compare D and if there is no match in column D, then copy and paste
"Compare" G3 & H3 to "Results D3 & E3.

I'm only looking to copy and paste those rows from "Compare" columns G & H
where there is no match. If there is a match then do nothing and move on to
the next H row on "Compare".

Any help is always greatly appreciated.

TIA!

Hi Stephen

Another way to do it listed below.

Option Explicit
Dim MyCell, MyRng1, MyRng2 As Range
Dim FoundCell As Range
Dim c As Integer
Private Sub CommandButton1_Click()

Worksheets("Compare").Activate

Set MyRng1 = Range("H2:H" & [G65535].End(xlUp).Row)
Set MyRng2 = Range("D6:D" & [D65535].End(xlUp).Row)

For Each MyCell In MyRng1

Set FoundCell = MyRng2.Find(What:=MyCell, LookAt:=xlWhole)

If FoundCell Is Nothing Then

c = Worksheets("Results").[D65535].End(xlUp).Row + 1

Worksheets("Results").Cells(c, 4).Value = _
MyCell.Offset(0, -1).Value
Worksheets("Results").Cells(c, 5).Value = _
MyCell.Value

End If

Next MyCell

End Sub

Hope this helps