Search, cut and paste in Excel

[hobman]

Hi, I'm new to this forum and had couple of questions. I have an excel workbook with two sheets ("first", "second"). Both of the sheets have a list of names. What I want to do is take each name from the first list and see if that name exists in the second list. when there is a match to cut that name out of the second list and place that name in one of the colums of first or second list.

I am not sure if this makes sense.

 

[xlbo]

Have a look at the following constructs:

For Each, Next

the FIND method

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

Hi and welcome to the forum!
Without anything more to work on the following will work through the "First" list and look for a match in the "Second" list

If there is one then the 2nd name is pasted next to the name in the original list.

This works on the assumption that the names are in col A in both lists and that both lists have only uniqe values. It is do-able if the values aren't unique but...

Sub FindStuff()
Dim lRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range
Dim found As Range

Set ws1 = Worksheets("First")
Set ws2 = Worksheets("Second")

lRow = ws1.Range("A65536").End(xlUp).Row
For Each c In ws1.Range("A1:A" & lRow)
    Set found = ws2.Columns(1).Cells.Find(c, , , xlWhole, xlByRows, xlNext)
        If Not found Is Nothing Then
            found.Cut ws1.Cells(c.Row, 2)
        End If
Next

Set ws1 = Nothing
Set ws2 = Nothing
Set found = Nothing

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[hobman]

I didn't understand what you meant. which constructs was I supposed to look at?

thanks.

 

[hobman]

Loomah, Tha last message was not directed at you. I am working on making your suggestion work. Thanks for the response.

 

[Loomah]

hobman
xlbo is suggesting you have a look into the constructs I have given you in my reply, being a For...Each...Next loop and the Find method.

xlbo's post & mine were obviously sent at the same time, though there may be 'someone' who'd suggest otherwise!!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

hobman
I realised that! I hope!!
Damn this tin-can-and-string connection. I'm off to watch the match
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

LOL

Loomah's just given you the fish supper - I was trying for the fishing rod !! ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question