A Simple 'Find' Problem

[morrisgr]

Hi.
I have a list of 30 Membership numbers on Sheet1, Column A.
I need a Macro to check each number individually to see if it occurs on Sheet 2 in Column A.
The answer needed is a simple 'found'=yes............or no

All the 'Find questions here are far more difficult to follow because of their detailed requirements.

Greatly appreciate any help.
Many thanks from sunny Spain.
Morris

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("A" & i)
            .Offset(, 1).Value = IIf(IsNumeric(Application.Match(.Value, Sheets("sheet2").Columns("A"), 0)), "Yes", "No")
        End With
    Next i
End With
End Sub

 

[morrisgr]

Wow, 7 minutes to get a reply. Fantastic.
A little more help if you can.
It works of course but puts "yes" in Column B on Sheet1.

What I need is that it will use variable (found) that will determine whether I copy the Member's info from Sheet1 to Sheet2.

Something like.....
If found="Yes" then Range("A2").Copy
Sheets("Sheet2").Activate
then Paste the info to the next free row (which I know how to do)

Many thanks.....
Morris

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("A" & i)
            If IsNumeric(Application.Match(.Value, Sheets("sheet2").Columns("A"), 0)) Then .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End With
End Sub

 

[morrisgr]

Hi Peter.
My apologies.
I realise that my wording wasn't quite accurate.
My Sheet1 has a list of ALL members.
My Sheet2 only has some of them.
If I select a Member's number from Sheet1, I want to chjeck if it is already on Sheet2, and if so.......don't paste it.
It should only paste if the Member is NOT on Sheet2.

Sorry, my fault.

Interesting that you use your co-ordinates in your profile.
Thanks for taking the trouble.
Morris
ps.....is it possible to give me a variable rather than simply pasting it in automatically? I can use that procedure elsewhere.

 

[VoG]

Try this

Sub test()
Dim LR As Long, i As Long, Found As Boolean
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("A" & i)
            Found = IsNumeric(Application.Match(.Value, Sheets("sheet2").Columns("A"), 0))
            If Not Found Then .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End With
End Sub

 

[morrisgr]

Excellent.
That's working perfectly.
Many thanks............until next time no doubt.
Regards
Morris