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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing rows on one sheet to list on other
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 |
Reply |
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 |