PDA

View Full Version : Solved: Update or insert



morsoe
10-06-2010, 02:16 PM
I have two worksheets. The first one is named "updates" and the second one is named "catalogue".

Column A in "updates" is named <product_key>. I want to match the <product_key> with column A in the worksheet "catalogue" which is also named <product_key>.

If there is a MATCH on the <product_key> the row in the worksheet "catalogue" should be updated with the entire row from the worksheet "updates".

If the is NO MATCH one the <product_key> the row from the worksheet "updates" should be copied and pasted to the first empty row in the worksheet "catalogue".

How can I do that?

PhilC
10-06-2010, 03:28 PM
This is what I've come up with. Let me know if it works for your application. Oh and you'll need to resort the catalog if you have it in alphabetical or numerical order.



Sub refer()
' set spreadsheets into variables
Set sh1 = Worksheets("updates")
Set sh2 = Worksheets("catalog")
' determine number of items on each sheet
rw1 = Val(sh1.UsedRange.Rows.Count)
rw2 = Val(sh2.UsedRange.Rows.Count)
'set up search loop
flag = 0 ' set up indicator flag
For I = 0 To rw1 ' check every line on the page
tval = sh1.Range("$A$1").Offset(I, 0).Value 'select first cell in the column of updates
For J = 0 To rw2 ' check each line in the catalog
If tval = sh2.Range("$A$1").Offset(J, 0).Value Then 'compair source value to end value
sh1.Range("$A$1").Offset(I, 0).EntireRow.Copy (sh2.Range("$A$1").Offset(J, 0)) 'copy updated entry
flag = 1 ' Set a flag indicate item was found
End If
Next J
If flag = 0 Then ' if item was not found in catalog
sh1.Range("$A$1").Offset(I, 0).EntireRow.Copy (sh2.Range("$A$1").Offset(rw2, 0)) ' add item to catalog
rw2 = rw2 + 1 ' add one to the number of catalog items
End If
flag = 0 ' reset indicator flag
Next I

morsoe
10-07-2010, 12:01 PM
It works. Thank you!

PhilC
10-07-2010, 01:19 PM
Glad I could help! :)

Don't forget to mark the thread as solved.

morsoe
10-22-2010, 04:03 AM
I have formulas in some of my cells, how can I insert and update (paste)only with the cell value in the VBA code?