The match is equivalent to this in a worksheet:

=match(1,(c2=c3:c88)*(d2=d3:d88),0)

(well, depending on the addresses of those variables.)

The c2=C3:c88 return an array of trues and falses.
the d2=d3:d88 return an array of trues and falses.

When you multiply the trues and falses together, you get another array of
1/0's. But to get a 1, both conditions had to be true.

The =match(1,thatarrayof1/0,0)
says to find the first 1 in that array--the first time both column C and D were
equal to C2 and D2 on the same row.

If it's an error, then there was no match.

If the match returns a number, then there was a match.

I don't know if this does what you want, though.

ClemCadidlhoper wrote:

Dave, Could you explain the following code. I am just not getting the
programming from all of the places I have looked for instruction, but it
seems that there is something in this code or not in this code, which is
keeping this from working the way I need it to.

For Each myCell In MstrRng.Cells
myFormula = "Match(1,(" & myCell.Address(external:=True) _
& "=" & SecondRng.Address(external:=True) & ")*(" _
& myCell.Offset(0, 1).Address(external:=True) _
& "=" & SecondRng.Offset(0, 1).Address(external:=True) _
& "),0)"
res = Application.Evaluate(myFormula)

If IsError(res) Then
'no match, do nothing
Else
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

"Dave Peterson" wrote:

Try this.

Option Explicit
Sub testme()

Dim MstrWks As Worksheet
Dim SecondWks As Worksheet
Dim myCell As Range
Dim MstrRng As Range
Dim SecondRng As Range
Dim DelRng As Range
Dim res As Variant
Dim myFormula As String

Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1")
Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2")

With MstrWks
Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With SecondWks
Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In MstrRng.Cells
myFormula = "Match(1,(" & myCell.Address(external:=True) _
& "=" & SecondRng.Address(external:=True) & ")*(" _
& myCell.Offset(0, 1).Address(external:=True) _
& "=" & SecondRng.Offset(0, 1).Address(external:=True) _
& "),0)"
res = Application.Evaluate(myFormula)

If IsError(res) Then
'no match, do nothing
Else
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
Application.Goto DelRng.EntireRow
'DelRng.EntireRow.Delete
End If

End Sub

Change the worksheet names and workbook names in these lines:
Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1")
Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2")

And I do a select the range instead of deleting it--nice for testing:

Application.Goto DelRng.EntireRow
'DelRng.EntireRow.Delete

Delete the .goto line and uncomment the next line after you've verified that it
works ok.

ClemCadidlhoper wrote:

I need to open a master name list and delete rows from it based on the
content of another worksheets. More specifically I need to have it look at
the info in columns A and B and if that same information is in columns A and
B of the second document, then delete the whole row from the first document.

--

Dave Peterson


--

Dave Peterson