Find and insert?

  • Thread starter Thread starter leonidas
  • Start date Start date
L

leonidas

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
 
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
 
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
 
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
 
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
 
Hi Bernie,

Thank you very much for all your help!
The code you gave me worked exactly as I wanted!
Thanks again!
 
Back
Top