Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare all matches
Hello all.
The macro below compares col. A of compare and master sheet with each other and then copy and pastes the corresponding cells. The problem is it copies and pastes the first match that it finds. Is there any way to copy and paste all the matches below each other? here is what I mean. compare sheet. A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Master sheet. A B C D Sam Sam Tom Compare sheet after running the program. A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 Here is my VBA Without pasting all matches. Option Explicit Sub CopyIDData() Dim rng As Range Dim rng1 As Range Dim cell As Range Dim res As Variant With Worksheets("Compare") Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("Master") Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each cell In rng.Cells If cell.Value = "" Then 'skip it Else res = Application.Match(cell, rng1, 0) If Not IsError(res) Then rng1(res, 2).Resize(1, 3).Copy Destination:=cell.Offset(0, 1) End If End If Next cell End Sub 'Thank you. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare all matches
Is this what you are trying to do?
http://www.rondebruin.nl/copy5.htm --JP "saman110 via OfficeKB.com" wrote: Hello all. The macro below compares col. A of compare and master sheet with each other and then copy and pastes the corresponding cells. The problem is it copies and pastes the first match that it finds. Is there any way to copy and paste all the matches below each other? here is what I mean. compare sheet. A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Master sheet. A B C D Sam Sam Tom Compare sheet after running the program. A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 Here is my VBA Without pasting all matches. Option Explicit Sub CopyIDData() Dim rng As Range Dim rng1 As Range Dim cell As Range Dim res As Variant With Worksheets("Compare") Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("Master") Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each cell In rng.Cells If cell.Value = "" Then 'skip it Else res = Application.Match(cell, rng1, 0) If Not IsError(res) Then rng1(res, 2).Resize(1, 3).Copy Destination:=cell.Offset(0, 1) End If End If Next cell End Sub 'Thank you. -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare all matches
sorry all text went together.
it should be like this. compare sheet. A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Master sheet. A B C D Sam Sam Tom Compare sheet after running the program. A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 saman110 wrote: Hello all. The macro below compares col. A of compare and master sheet with each other and then copy and pastes the corresponding cells. The problem is it copies and pastes the first match that it finds. Is there any way to copy and paste all the matches below each other? here is what I mean. compare sheet. A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Master sheet. A B C D Sam Sam Tom Compare sheet after running the program. A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 Here is my VBA Without pasting all matches. Option Explicit Sub CopyIDData() Dim rng As Range Dim rng1 As Range Dim cell As Range Dim res As Variant With Worksheets("Compare") Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("Master") Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each cell In rng.Cells If cell.Value = "" Then 'skip it Else res = Application.Match(cell, rng1, 0) If Not IsError(res) Then rng1(res, 2).Resize(1, 3).Copy Destination:=cell.Offset(0, 1) End If End If Next cell End Sub 'Thank you. -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare all matches
Since I don't know how to edit the posted threat, Here is the correct one.
Master sheet: A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Compare sheet: A B C D Sam Sam Tom Compare sheet after running the program: A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 saman110 wrote: sorry all text went together. it should be like this. compare sheet. A B C D sam 1 10 100 sam 2 20 200 sam 3 30 300 sam 4 40 400 Tom 5 6 7 Master sheet. A B C D Sam Sam Tom Compare sheet after running the program. A B C D Sam 1 10 100 Sam 2 20 200 3 30 300 4 40 400 Tom 5 6 7 Hello all. [quoted text clipped - 59 lines] 'Thank you. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Columns, Find Matches | Excel Programming | |||
Compare two lists for matches | Excel Programming | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
Compare columns, count matches | Excel Worksheet Functions | |||
I need to compare to columns and indicate the matches in another | New Users to Excel |