VBA to check if value in column exists and if not paste

[Drivingman]

HI All

Been looking all morning for this, and found a few things that I thought should work but not getting the outcome I want or how to amend it to suit perfectly.
We have a call log sheet that is received daily working on complaints. These are then amended to include any additional comments, or their status may change, the only constant is their identifier in column A:A.

The upload goes into sheet named "Upload", and I want it to check for any instance of the Identifier (from A3 downwards) in the identifier for any instance of it already happening in the sheet "Complaints 03.09.18". If the value is found then it will move down and look for the next value.
If the value is not found, then it will copy and paste from "Upload" all data from columns A:Q in that row, in the next clear row within Complaints sheet, and so on.

Once the sheet gets down to the final value - or first clear row to stop.
All data is within the same excel sheet now.
Hope this makes sense.
TYIA.

 

[Fluff]

Are the col A identifiers unique?

 

[Fluff]

Assuming that the answer to my question is yes, try

Sub CheckUpload()
   Dim cl As Range
   Dim Uws As Worksheet, Cws As Worksheet
   
   Set Uws = Sheets("Upload")
   Set Cws = Sheets("Complaints")
   
   With CreateObject("scripting.dictionary")
      For Each cl In Uws.Range("A2", Uws.Range("A" & Rows.count).End(xlUp))
         .Item(cl.Value) = Empty
      Next cl
      For Each cl In Cws.Range("A2", Cws.Range("A" & Rows.count).End(xlUp))
         If .exists(cl.Value) Then .Remove cl.Value
      Next cl
      If Uws.AutoFilterMode Then Uws.AutoFilterMode = False
      Uws.Range("A1:Q1").AutoFilter 1, .keys, xlFilterValues
      Uws.AutoFilter.Range.Offset(1).Copy Cws.Range("A" & Rows.count).End(xlUp).Offset(1)
      Uws.AutoFilterMode = False
   End With
End Sub

 

[Drivingman]

HI

Thanks for this but when I copy this in and run it I get the following error:

Run-time error '13'

Type mismatch

When I debug it highlights:
Uws.Range("A1:Q1").AutoFilter 1, .keys, xlFilterValues

 

[Drivingman]

Yes you are correct all col A identifiers are unique.

 

[Fluff]

Are you sure that there are values in the upload sheet that are not present in the complaints sheet?

 

[Drivingman]

You are a genius. I should have checked that first and not assumed that there were new individual values today. Normally are - by the bucket load!!!!!

 

[Fluff]

Add the line in blue & if there is nothing to add it will bypass the final portion of code.

Sub CheckUpload()
   Dim cl As Range
   Dim Uws As Worksheet, Cws As Worksheet
   
   Set Uws = Sheets("pcode")
   Set Cws = Sheets("Complaints")
   
   With CreateObject("scripting.dictionary")
      For Each cl In Uws.Range("A2", Uws.Range("A" & Rows.count).End(xlUp))
         .Item(cl.Value) = Empty
      Next cl
      For Each cl In Cws.Range("A2", Cws.Range("A" & Rows.count).End(xlUp))
         If .exists(cl.Value) Then .Remove cl.Value
      Next cl
      If Uws.AutoFilterMode Then Uws.AutoFilterMode = False[COLOR=#0000ff]
      If .count = 0 Then MsgBox "Nothing to add": Exit Sub[/COLOR]
      Uws.Range("A1:Q1").AutoFilter 1, .keys, xlFilterValues
      Uws.AutoFilter.Range.Offset(1).Copy Cws.Range("A" & Rows.count).End(xlUp).Offset(1)
      Uws.AutoFilterMode = False
   End With
End Sub