Solved: Update or insert

[morsoe]

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]

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.
[VBA]

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

[/VBA]

[morsoe]

It works. Thank you!

[PhilC]

Glad I could help!

Don't forget to mark the thread as solved.

[morsoe]

I have formulas in some of my cells, how can I insert and update (paste)only with the cell value in the VBA code?