Comparing rows on one sheet to list on other
 

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

Dave Peterson Wrote:
Tom's code is comparing the values in column H (of Elements) with 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 found 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 figured 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 in message
...
Ok, I asked this already, however since I didn't get an 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 2500 rows,
1
column, and Exempt is empty.

What I need to do, is for each row in Elements, compare column H
to
the
Servers sheet. If there is a match, I need to move the active 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

Comparing rows on one sheet to list on other
 

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 cell value
from H like it should, but the result of the Match always returns that
"Error 2042" so it's not seen as a match.. I found this out by putting
MsgBox CStr(res) right at the end of the for loop, so I could see what
the value of res was every part of the loop. I know that cell H1 will
not be a match, and that H2 will be a match.. and on both I get that
error code popping up in my MsgBox.

Dave Peterson Wrote:
Tom's code is comparing the values in column H (of Elements) with 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 found 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 figured 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 in message
...
Ok, I asked this already, however since I didn't get an 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 2500 rows,
1
column, and Exempt is empty.

What I need to do, is for each row in Elements, compare column H
to
the
Servers sheet. If there is a match, I need to move the active 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