Selected Answer
Hasson
Your current macro doesn't correctly "pull values from sheet1" where they exist in sheet RES since your last write to the array reads:
res(i, 3) = s1(i, 3)
should read:
res(i, 3) = s1(j, 3)
Also it makes no attempt to write any Items which don't already exist.
In the revised file attached, I've looped through your array s1 (from Sheet1) first, assuming there will be the same or more items in that sheet than RES. I then loop though the array of RES items to check is they exist there (and adding them if not). I've added some comments (and bits in bold) to help you:
Sub add()
Dim i&, j&, s1(), res()
Dim Found As Boolean
' copy new data to array s1
With Sheets("Sheet1")
s1 = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
End With
With Sheets("RES")
ReDim res(1 To .Cells(Rows.Count, "B").End(xlUp).Row, 1 To 3)
' copy existing data to array res
res = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
' loop through s1 ###
For i = 1 To UBound(s1)
' reset flag
Found = False
' check against res ###
For j = 1 To UBound(res)
' if Item matches, copy new Import/Export values to array res
If res(j, 1) = s1(i, 1) Then
res(j, 2) = s1(i, 2)
res(j, 3) = s1(i, 3)
' flag the match
Found = True
Exit For
End If
Next j
' if doesn't exist in res...
If Found = False Then
' redimension array to increase by 1
res = WorksheetFunction.Transpose(res)
ReDim Preserve res(1 To UBound(res, 1), 1 To UBound(res, 2) + 1)
res = WorksheetFunction.Transpose(res)
' write value to new array element
res(UBound(res), 1) = s1(i, 1)
res(UBound(res), 2) = s1(i, 2)
res(UBound(res), 3) = s1(i, 3)
End If
Next i
.Range("B2:D1000000").ClearContents
'copy revised res to sheet
.Range("B2").Resize(UBound(res), UBound(res, 2)).Value = res
End With
' tell user is more items in s1 than res
If UBound(res) <> UBound(s1) Then
j = UBound(s1) - UBound(res)
MsgBox 2 * j & " duplicate Items in new data (last values copied to RES)"
End If
End Sub
The new items are added without borders or numbers in column A but that makes them easy to see.
Hope this fixes your problem.
