LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default Please help with this...

I wrote this in responce to R.VENKATARAMAN they wrote a
reply and then forgot about the post.

Here is my problem

-

I have two workbooks. One contains around 5500 rows of
records (1.xls) and another (2.xls) containing 400 rows.

I need to separate out from 1.xls all of the records it
has which match those in 2.xls. Sometimes there will be
up to 6 rows in 1.xls matching that in 2.xls. The column
which I need to match the records to is column H in
1.xls, the column to get the search criteria from is in
column B in 2.xls.

Any matching rows found in 1.xls I would like to add to a
new sheet in 1.xls deleting the original row from the
main sheet, carrying on to the end extracting any others
matching before moving on to the next in 2.xls to search
for that one.

I have been using the following code to loop through
2.xls, sheet 1, column B taking the value, Checking in
1.xls through all records for a match in column H. I am
not sure if the code will only work if it is in a module
of one or the other workbooks; but I cant get it to work.
It just seems to cut and paste the row from 2.xls into a
new sheet in 1.xls when it finds the first match in
1.xls.



code:
----------------------------------------------------------
----------------------

Sub MoveMatches()

Dim wsSrc As Worksheet
Dim wsFind As Worksheet
Dim wsDest As Worksheet
Dim rCell As Range
Dim rFound As Range

Set wsSrc = Workbooks("2.xls").Sheets(1)
Set wsFind = Workbooks("1.xls").Sheets(1)
Set wsDest = Workbooks("1.xls").Sheets(2)

For Each rCell In wsSrc.Columns(1).Cells
Set rFound = wsFind.Columns(2).Find(rCell.Value)

If Not rFound Is Nothing Then
rCell.EntireRow.Cut Destination:=wsDest.Range
("A65536").End(xlUp).Offset(1, 0)
End If

Next rCell

End Sub

----------------------------------------------------------
----------------------


Thanks

Mary

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 05:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"