You were quite right, it actually came to me when I decided to take
break and not think about it (that's how it always goes).. I had som
trailing spaces that I wasn't aware of, and that's why the match wasn'
working.. Of course, as soon as I got back to it, fixed it, there wa
your post talking about spaces.

So thanks to Tom for the code, which works like a champ and thanks t
Dave for the extra help.

Dave Peterson Wrote:
What two values are you comparing (that you know match)?

is it possible that one is a string ('123 and one is a value 123).

How about Leading/trailing/embedded spaces.

If you pick out those cells (say A2 and H9), if you put:

=Elements!h9=servers!a2

What do you get (true/false)?



stevem wrote:

Yes, that is exactly what I wanted, and it's picking up the cel
value
from H like it should, but the result of the Match always return
that
"Error 2042" so it's not seen as a match.. I found this out b
putting
MsgBox CStr(res) right at the end of the for loop, so I could se
what
the value of res was every part of the loop. I know that cell H
will
not be a match, and that H2 will be a match.. and on both I ge
that
error code popping up in my MsgBox.

Dave Peterson Wrote:
Tom's code is comparing the values in column H (of Elements) wit
the
values in
column A of (of Servers).

Is that what you wanted?

If no, then change those columns ("H" or "A") to what you want.

stevem wrote:

Ok, after a little investigation (mainly adding some MsgBoxes
in
the
code to see what variables are what at specific times, I foun
out
that
the variable res is being set to "Error 2042" on every pass,
reguardless if there is a match or not.

Don't know why, or what that is, or how to fix it, but figure
I'd
give
you as much info as I possibly can.

Tom Ogilvy Wrote:
are all the worksheets in the same workbook. In

Sub MoveExempt()
Dim rng as Range, rng1 as Range
Dim rw as Long
with worksheets("Elements")
set rng = .Range(.Cells(1,"H" _
),.Cells(rows.count,"H").End(xlup)
End With
With Worksheets("Servers")
set rng1 = .Range(.Cells(1,"A" _
),.Cells(rows.count,"A").End(xlup))
End With
rw = 1
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.EntireRow.Copy Destination:= _
Worksheets("Exempt").Cells(rw,1)
rw = rw + 1
cell.EntireRow.clearcontents
end if
Next
on Error Resume Next
rng.SpecialCells(xlBlanks).Entirerow.Delete
On Error goto 0
End Sub

test this on a copy of your workbook.

--
Regards,
Tom Ogilvy



"stevem " wrote i
message
...
Ok, I asked this already, however since I didn't get a
answer
that
worked, I'm going to try to clarify as best as possible.

I have 3 sheets, one called Elements, one called Servers
and
one
called Exempt.

Elements has 10K+ rows, with 12 columns. Servers has 250
rows,
1
column, and Exempt is empty.

What I need to do, is for each row in Elements, compar
column H
to
the
Servers sheet. If there is a match, I need to move the activ
row
to
the
Exempt sheet.

Hopefully this clarifies enough. Having alot of problem with
this
myself as I'm in brain meltdown right now.

Thanks in advance.


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


--
stevem


------------------------------------------------------------------------
stevem's Profile:
http://www.excelforum.com/member.php...fo&userid=7725
View this thread:
http://www.excelforum.com/showthread...hreadid=260760

--

Dave Peterson


--
stevem

------------------------------------------------------------------------
stevem's Profile:
http://www.excelforum.com/member.php...fo&userid=7725
View this thread:
http://www.excelforum.com/showthread...hreadid=260760

--

Dave Peterson



--
stevem
------------------------------------------------------------------------
stevem's Profile: http://www.excelforum.com/member.php...fo&userid=7725
View this thread: http://www.excelforum.com/showthread...hreadid=260760