Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
Hi, I have a sheet1 with data in column B. I have a sheet2 with also dat in column B. I have some code below to check if the data from sheet also exists on sheet1, and if not, then copy it to sheet1. The only problem is it works 1 time and then I get an error. I am novice in VBE so that's why the code will look unlogical. Can someone help me to create a better code that will work? Thanks i advance! Code ------------------- Sub FindAndInsert() For Each cell In Range("A3:A14") myvalue = cell.Value On Error GoTo NextPart myvalue1 = Sheets("Sheet1").Columns("A:A").Find(What:=myvalue ) GoTo Finalize NextPart: cell.Offset(-1, 0).Select myvalue2 = Selection.Value Sheets("Sheet1").Select Columns("A:A").Find(What:=myvalue2).Select Selection.Offset(1, 0).EntireRow.Insert Selection.Offset(1, 0).Select Selection.Value = myvalue Sheets("Sheet2").Select Finalize: Next cell End Su ------------------- -- leonida ----------------------------------------------------------------------- leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=56783 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
Sub FindAndInsert()
Dim cell As Range For Each cell In Sheets("Sheet2").Range("A3:A14") If IsError(Application.Match(cell.Value, _ Worksheets("Sheet1").Range("A:A"), False)) Then cell.Copy Worksheets("Sheet1").Range("A65536").End(xlUp)(2) End If Next cell End Sub HTH, Bernie MS Excel MVP "leonidas" wrote in message ... Hi, I have a sheet1 with data in column B. I have a sheet2 with also data in column B. I have some code below to check if the data from sheet2 also exists on sheet1, and if not, then copy it to sheet1. The only problem is it works 1 time and then I get an error. I am a novice in VBE so that's why the code will look unlogical. Can someone help me to create a better code that will work? Thanks in advance! Code: -------------------- Sub FindAndInsert() For Each cell In Range("A3:A14") myvalue = cell.Value On Error GoTo NextPart myvalue1 = Sheets("Sheet1").Columns("A:A").Find(What:=myvalue ) GoTo Finalize NextPart: cell.Offset(-1, 0).Select myvalue2 = Selection.Value Sheets("Sheet1").Select Columns("A:A").Find(What:=myvalue2).Select Selection.Offset(1, 0).EntireRow.Insert Selection.Offset(1, 0).Select Selection.Value = myvalue Sheets("Sheet2").Select Finalize: Next cell End Sub -------------------- -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=567838 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
Hi Bernie, Thanks for your help! I only have one question. With your code the data on sheet2 that not exists on sheet1 is pu underneath the column with data on sheet1. Is it also possible to put the data on the right place in the column? For example: If column B on sheet1 has the following data: a b d e and column B on sheet2 has the following data: a b c d e Your code gives this result: a b d e c But I would like to have the result: a b c d e Is this possible? Thanks in advance again for your help -- leonida ----------------------------------------------------------------------- leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=56783 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
leonidas,
But what if you have: If column B on sheet1 has the following data: a b d e and column B on sheet2 has the following data: q r s t e What would you want then? Anyway, you could sort the data after you complete the transfer. Or if you just expect the same values, copy the whole table instead of stepping through it. HTH, Bernie MS Excel MVP "leonidas" wrote in message ... Hi Bernie, Thanks for your help! I only have one question. With your code the data on sheet2 that not exists on sheet1 is put underneath the column with data on sheet1. Is it also possible to put the data on the right place in the column? For example: If column B on sheet1 has the following data: a b d e and column B on sheet2 has the following data: a b c d e Your code gives this result: a b d e c But I would like to have the result: a b c d e Is this possible? Thanks in advance again for your help! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=567838 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
Hi Bernie, Sorry, I think I haven't explained the situation enough. Sheet2 is a copy of sheet1 (this is to track changes afterwards an sheet1 cannot be changed after making the copy (protected)). Sheet2 ha also another user than sheet1. The user of sheet2 can delete and inser rows in column A (only on specific rows, the rest is protected). Whe the user of sheet2 is ready, a resume is made on a new sheet. The data of sheet1 is copied to this new sheet, but because the user o sheet2 has made some changes, these changes should also be visible i the resume. Deleting data in sheet2 is no problem, because the origina data is in sheet1. But inserting is a problem. These inserted dat should be copied to the resume. For example: If sheet1 has data: a (protected) b c d (protected) e f g h and the user of sheet2 changes this to: a (protected) c d (protected) x y z e f g h it should be possible to allways copy the new data to the right plac when checking the data a row above like in the code below. Only thi code doesn't work. Could you please help me fix the code. Thanks again! Code ------------------- Sub FindAndInsertOK() Dim cell As Range For Each cell In Sheets("Sheet2").Range("A3:A14") myvalue = cell.Value If IsError(Application.Match(myvalue, _ Worksheets("Sheet1").Range("A:A"), False)) Then cell.Offset(-1, 0).Select myvalue1 = Selection.Value Sheets("Sheet1").Select Columns("A:A").Find(What:=myvalue1).Select Selection.Offset(1, 0).EntireRow.Insert Selection.Offset(1, 0).Select Selection.Value = myvalue End If Next cell End Su ------------------- -- leonida ----------------------------------------------------------------------- leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=56783 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
I'm assuming you have a header row that is the same (cell A2)....
Sub FindAndInsertVer2() Dim cell As Range Dim myCell As Range For Each cell In Sheets("Sheet2").Range("A3:A14") If IsError(Application.Match(cell.Value, _ Worksheets("Sheet1").Range("A:A"), False)) Then Set myCell = Worksheets("Sheet1").Range("A:A").Find(cell(0, 1).Value) myCell(2, 1).EntireRow.Insert cell.Copy myCell(2, 1) End If Next cell End Sub HTH, Bernie MS Excel MVP "leonidas" wrote in message ... Hi Bernie, Sorry, I think I haven't explained the situation enough. Sheet2 is a copy of sheet1 (this is to track changes afterwards and sheet1 cannot be changed after making the copy (protected)). Sheet2 has also another user than sheet1. The user of sheet2 can delete and insert rows in column A (only on specific rows, the rest is protected). When the user of sheet2 is ready, a resume is made on a new sheet. The data of sheet1 is copied to this new sheet, but because the user of sheet2 has made some changes, these changes should also be visible in the resume. Deleting data in sheet2 is no problem, because the original data is in sheet1. But inserting is a problem. These inserted data should be copied to the resume. For example: If sheet1 has data: a (protected) b c d (protected) e f g h and the user of sheet2 changes this to: a (protected) c d (protected) x y z e f g h it should be possible to allways copy the new data to the right place when checking the data a row above like in the code below. Only this code doesn't work. Could you please help me fix the code. Thanks again! Code: -------------------- Sub FindAndInsertOK() Dim cell As Range For Each cell In Sheets("Sheet2").Range("A3:A14") myvalue = cell.Value If IsError(Application.Match(myvalue, _ Worksheets("Sheet1").Range("A:A"), False)) Then cell.Offset(-1, 0).Select myvalue1 = Selection.Value Sheets("Sheet1").Select Columns("A:A").Find(What:=myvalue1).Select Selection.Offset(1, 0).EntireRow.Insert Selection.Offset(1, 0).Select Selection.Value = myvalue End If Next cell End Sub -------------------- -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=567838 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and insert?
Hi Bernie, Thank you very much for all your help! The code you gave me worked exactly as I wanted! Thanks again! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=567838 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find name and insert address | Excel Worksheet Functions | |||
find text then insert new row above | Excel Programming | |||
Find & Insert - Is this possible?? | Excel Discussion (Misc queries) | |||
How do I find a value and insert new value | Excel Worksheet Functions | |||
find row value, insert column | Excel Programming |