? for Jim Thomlinson

[JT]

Jim........I'm still struggling with the code for matching
data on 2 different sheets.

Sheet(1) 1, A, 2, B, 3, C, D, 4, 5, 11 (This is Col A)

Sheet(2) 1, 2, 3, 4, 5, 6, 7, 10, 11, 12 (This is Col A)

Once the matching process is completed, Sheet (1) (Col A)
should look like this: A, B, C, D (because these values
are not in Sheet (2) Col A. There are no changes to Sheet
(2) Col A.

I tried the new code you suggested and what I ended up
with on Sheet (1) is 1. Once it found a "No Match" item
it deleted every row after and including the first "No
Match" item.

I really appreciate all of your help with this. I'm
struggling with understanding the "if not xxxx is nothing"
part of the code.

Thanks again for all of your help........

 

[Guest]

Let me take a look at this and I will get back to you...

 

[JT]

Thank you

-----Original Message-----
Let me take a look at this and I will get back to you...


.

 

[Guest]

Yeah that last code was not quite right...

Sub Test()

Dim rngToFind As Range
Dim rngFound As Range

Set rngToFind = Sheets("1").Range("A65535").End(xlUp)
Do While rngToFind.Row > 1
Set rngFound = Sheets("2").Columns(1).Find(rngToFind.Value)
Set rngToFind = rngToFind.Offset(-1, 0)
If Not rngFound Is Nothing Then
rngToFind.Offset(1, 0).EntireRow.Delete
End If
Loop
End Sub

I am using the "is nothing" to indicate whether I have found the item we are
searching for. "Find" returns a range object (in this case a single cell). I
am trying to set my range object = to that cell. If nothing is found then my
range object is nothing. So "not rngFound is nothing" indicates that the find
fucntion found a match, so now we need to delete... My appologies for
yesterdays code I was just leaving the office and did not check it out...


HTH

 

[JT]

Thank you very much. That works great.

-----Original Message-----
Yeah that last code was not quite right...

Sub Test()

Dim rngToFind As Range
Dim rngFound As Range

Set rngToFind = Sheets("1").Range("A65535").End(xlUp)
Do While rngToFind.Row > 1
Set rngFound = Sheets("2").Columns(1).Find (rngToFind.Value)
Set rngToFind = rngToFind.Offset(-1, 0)
If Not rngFound Is Nothing Then
rngToFind.Offset(1, 0).EntireRow.Delete
End If
Loop
End Sub

I am using the "is nothing" to indicate whether I have found the item we are
searching for. "Find" returns a range object (in this case a single cell). I
am trying to set my range object = to that cell. If nothing is found then my
range object is nothing. So "not rngFound is nothing" indicates that the find
fucntion found a match, so now we need to delete... My appologies for
yesterdays code I was just leaving the office and did not check it out...


HTH


.