Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows on one sheet to list on other
Ok, after a little investigation (mainly adding some MsgBoxes) in th code to see what variables are what at specific times, I found out tha 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 giv 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 t the Servers sheet. If there is a match, I need to move the active row t 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/ -- steve ----------------------------------------------------------------------- stevem's Profile: http://www.excelforum.com/member.php...nfo&userid=772 View this thread: http://www.excelforum.com/showthread.php?threadid=26076 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
Search a worksheet, extract rows using a list from another sheet | Excel Discussion (Misc queries) | |||
Comparing rows on one sheet to list on other | Excel Programming | |||
Comparing rows on one sheet to list on other | Excel Programming |