Only matach two excel spread sheet

L

Lillian Lian

I have sheet1 and sheet2 both as SSN on columnA like this
sheet1
111-111-111
222-222-222
333-333-333

sheet2
111-111-111
222-222-222
444-444-444

I would like match on sheet3 showing
111-111-111
222-222-222

how do you write the macro for this?

Thanks.

Lillian
 
J

Joel

Sub matchsheets()

Sh1RowCount = 1
Sh3RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) <> ""
SSN = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=SSN, _
LookIn:=xlValues)
If Not c Is Nothing Then
With Sheets("Sheet3")
.Range("A" & Sh3RowCount) = SSN
Sh3RowCount = Sh3RowCount + 1
End With
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub
 
L

Lillian Lian

Thanks Joel, it work, your great

Joel said:
Sub matchsheets()

Sh1RowCount = 1
Sh3RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) <> ""
SSN = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=SSN, _
LookIn:=xlValues)
If Not c Is Nothing Then
With Sheets("Sheet3")
.Range("A" & Sh3RowCount) = SSN
Sh3RowCount = Sh3RowCount + 1
End With
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top