Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy no match
sheet1
column A column B R1 R2 ABC 10 R3 DEF 12 R4 LMN sheet2 column A column B R1 R2 ABC 9 R3 XYZ 13 R4 DEF 11 R5 PQR 15 I've to find out all the column A values of sheet2 that are not in column A of sheet1 (in this example XYZ and PQR) and paste the entire row of sheet 2 in the next null row under column B of sheet1. Since null row under column B of sheet 1 is R4, R4 will be overwritten. Thus the output will look like sheet1 column A column B R1 R2 ABC 10 R3 DEF 12 R4 XYZ 13 R5 PQR 15 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy no match
Hi Vernal,
Try this... Sub test() On Error Resume Next Dim S1 As Worksheet Dim S2 As Worksheet Dim cell As Object Set S1 = Sheets("Sheet1") Set S2 = Sheets("Sheet2") For Each cell In S2.Range("B1:B" & S2.Range("B65536").End(xlUp).Row) RowInS2 = 0 RowInS2 = S1.Columns("b:b").Find(what:=cell.Value, lookat:=xlWhole).Row If RowInS2 = 0 Then S2.Rows(cell.Row).Copy S1.Select Range("C65536").End(xlUp).Offset(1, -2).Select ActiveSheet.Paste S2.Select End If Next cell Application.CutCopyMode = False End Sub Regards Ankur Kanchan www.xlmacros.com wrote: sheet1 column A column B R1 R2 ABC 10 R3 DEF 12 R4 LMN sheet2 column A column B R1 R2 ABC 9 R3 XYZ 13 R4 DEF 11 R5 PQR 15 I've to find out all the column A values of sheet2 that are not in column A of sheet1 (in this example XYZ and PQR) and paste the entire row of sheet 2 in the next null row under column B of sheet1. Since null row under column B of sheet 1 is R4, R4 will be overwritten. Thus the output will look like sheet1 column A column B R1 R2 ABC 10 R3 DEF 12 R4 XYZ 13 R5 PQR 15 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy no match
works well. thanks.
ankur wrote: Hi Vernal, Try this... Sub test() On Error Resume Next Dim S1 As Worksheet Dim S2 As Worksheet Dim cell As Object Set S1 = Sheets("Sheet1") Set S2 = Sheets("Sheet2") For Each cell In S2.Range("B1:B" & S2.Range("B65536").End(xlUp).Row) RowInS2 = 0 RowInS2 = S1.Columns("b:b").Find(what:=cell.Value, lookat:=xlWhole).Row If RowInS2 = 0 Then S2.Rows(cell.Row).Copy S1.Select Range("C65536").End(xlUp).Offset(1, -2).Select ActiveSheet.Paste S2.Select End If Next cell Application.CutCopyMode = False End Sub Regards Ankur Kanchan www.xlmacros.com wrote: sheet1 column A column B R1 R2 ABC 10 R3 DEF 12 R4 LMN sheet2 column A column B R1 R2 ABC 9 R3 XYZ 13 R4 DEF 11 R5 PQR 15 I've to find out all the column A values of sheet2 that are not in column A of sheet1 (in this example XYZ and PQR) and paste the entire row of sheet 2 in the next null row under column B of sheet1. Since null row under column B of sheet 1 is R4, R4 will be overwritten. Thus the output will look like sheet1 column A column B R1 R2 ABC 10 R3 DEF 12 R4 XYZ 13 R5 PQR 15 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match with copy | Excel Discussion (Misc queries) | |||
Match and copy | Excel Worksheet Functions | |||
Match and Copy | Excel Worksheet Functions | |||
Match and Copy | Excel Programming | |||
Maybe this isn't possible to match name and copy? | Excel Programming |