Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Compare cell a1 to entire A col in sheet2 [Solved/Closed]

Report
-
 Guest -
Hello,

I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet.

I have experimented with many bits of macro code from others but so far no real success.


Figuring out how to say that in code is a bit difficult for me. I'm struggling to get the hang of this.

Maybe...

sub compare()
Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As range

Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 As range
Dim C_2 As range

Set Sh_1 = ActiveWorkbook.Sheets("Master")
Set Sh_2 = ActiveWorkbook.Sheets("Inventory")

LastRow_1 = Sh_1.range("A5000").End(xlUp).Row
LastCol_1 = Sh_1.range("A5000").End(xlToLeft).Column
Set Data_1 = Sh_1.range("A2").Resize(LastRow_1, LastCol_1)

LastRow_2 = Sh_2.range("A5000").End(xlUp).Row
LastCol_2 = Sh_2.range("A5000").End(xlToLeft).Column
Set Data_2 = Sh_2.range("A2").Resize(LastRow_2, LastCol_2)

For Each C_1 In Data_1
For Each C_2 In Data_2
If C_2 = C_1 Then
'found a cell on sheet2 that matched cell in sheet1
'now do what you need to do
Data_2.EntireRow.Copy Destination:=Worksheets("New_Master").range("A5000").End(xlUp).Offset(1, 0)

End If


Next C_2
Next C_1
end sub


When I run this it just copies everything to the sheet called New_Master, including the non-matches.

What am I missing? It has to be obvious, that's usually the case. ;-)

Thanks,

Bill
See more

2 replies

Answer 1 / 2
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
 109
Hello Bill,
when you write 
Data_2.EntireRow.Copy

you copy the whole range "Data_2", which corresponds to the column A of the sheet "Inventory"
If you want to copy only the rows in Data_2 that match with Data_1, do write 
C_2.EntireRow.Copy

I hope it helps
Ivan
Thank you
9
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

Ivan- I am new to Macro, Can you explain little more how this will work?

Thanks
Vijay
Report
Posts
1
Registration date
Wednesday May 11, 2011
Status
Member
Last seen
May 11, 2011
Thanks Bill & Ivan for this macro. This works out beautifully. I have an additional requirement. Here you compare sheet 1 & 2 and copy value from 2 to sheet 3 if present in sheet 1. Now if I want to copy the matching values from both 1&2 to the new sheet what would I add/modify in the last copy statement ?

thanks
Gaurav
Report

I tried this out , but it only copies the 1st Row (Sheet 1) and pastes in Sheet 2:

Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As Range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As Range

Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 As Range
Dim C_2 As Range


Set Sh_1 = ActiveWorkbook.Sheets("Sheet1")
Set Sh_2 = ActiveWorkbook.Sheets("Sheet2")

LastRow_1 = Sh_1.Range("A200").End(xlUp).Row
LastCol_1 = Sh_1.Range("O200").End(xlToLeft).Column
Set Data_1 = Sh_1.Range("A1").Resize(LastRow_1, LastCol_1)

LastRow_2 = Sh_2.Range("A200").End(xlUp).Row
LastCol_2 = Sh_2.Range("O200").End(xlToLeft).Column
Set Data_2 = Sh_2.Range("A1").Resize(LastRow_2, LastCol_2)

For Each C_1 In Data_1
For Each C_2 In Data_2
If C_1.Value <> C_2.Value Then
C_1.EntireRow.Copy Destination:= _
Sh_2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If
Next C_2
Next C_1

End Sub
Report

Can Any one share some guidence on the above problem of Guest: it only copies the 1st Row (Sheet 1) and pastes in Sheet 2????
Answer 2 / 2

Ivan,

COOL! Thanks!

Bill
Report

very use full macro . thank you for sharing.
Ask a question