Opening file when file name is always different

First, I'd bet that my sample code blew up real good because I never set these
variables:

Dim NextWks As Worksheet
Dim PrevWks As Worksheet

You need something like:

set Newwks= worksheets("sheet1")
set prevwks = worksheets("sheet2")

And I had a couple of typos in the version that worked(?) from the top down:

if delrng is nothing then
set myrng = mycell
else
set myrng = union(mycell,delrng)
end if

Should read as:

if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell,delrng)
end if

But I don't think you can use matching up by row numbers to find the records to
delete--especially since the number of entries changes (but I could be wrong).
I'd look for the values in column B and compare them, then if they match, slide
of to column D and compare them again:

If that's true:

Option Explicit
Sub testme03()

Dim NextWks As Worksheet
Dim PrevWks As Worksheet
Dim res As Variant
Dim nextColB As Range
Dim prevColB As Range
Dim myCell As Range
Dim delRng As Range

'make these what you want
Set NextWks = Worksheets("sheet1")
Set PrevWks = Worksheets("sheet2")

With PrevWks
Set prevColB = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
End With

With NextWks
Set nextColB = .Range("b:b") 'whole column
End With

For Each myCell In prevColB.Cells
res = Application.Match(myCell.Value, nextColB, 0)
If IsError(res) Then
'not found, do nothing
Else
If nextColB(res).Offset(0, 2).Value _
= myCell.Offset(0, 2).Value Then
'we have a double match
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
End If
Next myCell

If delRng Is Nothing Then
MsgBox "nothing to delete"
Else
delRng.EntireRow.Delete
End If

End Sub



"Lee Jeffery " wrote:

Dave P,

I'm doing something very, very wrong with your code.
Each one of your suggestions gives me a run-time error 91: Object
variable or With block variable not set on the For Each line. I've
tried to use the set command but still no success. Which variable am I
missing and how should this be set, please?

Many thanks. All help greatly appreciated.

Lee

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Thanks, Dave.

Although I'm close to babbling now, I'd finally worked out the Set
command but the results were leaving more info than I want. For testing
purposes, I have created 2 workbooks which are almost identical but I
have made manual adjustments to ten entries and coloured these so they
should be the only ones left in a successful exception report. These
entries are appearing but so is a large amount of info that should be
deleted.
I'll try your latest suggestion and see if I can be successful with
this.
Many thanks for your incredible patience with this.

Lee Jeffery


---
Message posted from http://www.ExcelForum.com/