Copy entry into a range if there is no duplication

[GIS2013]

Hi,
I'm trying to create a "dynamic" db. Db is located in sheet1. Into sheet2, which has the same headers as sheet1, new monthly data will be thrown. Sheet1 needs to pull only entries which aren't already in db, hence no duplication.
Hope I've managed to explain myself

Sub l_feed()
With Worksheets("l_feed")
Dim rng As Range, r As Range
Set rng = .Range("a1:a" & .UsedRange.Rows.Count)
For Each r In rng
if r.Value 'here r.value should check if it's not already in a certain range in the dbsheet... 
Next r
End With
End Sub

[HaHoBe]

Gi, GIS2013,

maybe like this:

Sub l_feed()
With Worksheets("l_feed")
Dim rng As Range, r As Range
  Set rng = .Range("a1:a" & .UsedRange.Rows.Count)
  For Each r In rng
    If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), r) = 0 Then
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).EntireRow.Value = r.EntireRow.Value
    End If
  Next r
End With
End Sub

Ciao,
Holger

[GIS2013]

Hi there Holger, could you please adjust your code in accordance with my sample (I've already done part of this). Cheers.

[HaHoBe]

Hi, GIS2013,

sorry but I donīt understand what you want me to do: you asked for a code to compare the values in Columns A of sheets db and feed, thatīs what the code does. This doesnīt seem to be what you are after. So you would have to explain what you really want.

Ciao,
Holger

[GIS2013]

Sure. I should add a sample file in the first place . Anyway, if you look at "db" you can see 8 entries. In "feed" there are currently 2 entries. I need the code to copy from "feed" to "db" any entry upon the condition that it's not already in "db". And so the sub should not copy worksheets("feed").range("a2:c2") since it's already in "db", but it should copy worksheets("feed").range("a3:c3") into a new line in "db" (hence to become the last line). Thanks.

**"feed"'s entry to be declared non-duplicated (and so be copied) if at least one of its values (in column A or B or C) are not exactly as in "db".

[HaHoBe]

Hi, GIS2013,

maybe like this:

Sub l_feed()
Dim r           As Range
Dim blnCopy     As Boolean
Dim var         As Variant
Dim lngCompare  As Long
Dim ws          As Worksheet

lngCompare = 1

Set ws = Sheets("db")

With Worksheets("feed")
  For Each r In .Range("a2:a" & .UsedRange.Rows.Count)
    blnCopy = False
    Do Until IsError(var)
      var = Application.Match(CDbl(r.Value), ws.Range("A" & lngCompare & ":A" & ws.Range("A" & Rows.Count).End(xlUp).Row), 0)
      If Not IsError(var) Then
        If r.Offset(0, 1).Value = ws.Cells(var, "B").Value And r.Offset(0, 2).Value = ws.Cells(var, "C").Value Then
          blnCopy = True
        End If
        If blnCopy Then lngCompare = lngCompare + var
      End If
    Loop
    If IsError(var) And Not blnCopy Then
      ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).Value = r.Resize(1, 3).Value
    End If
  Next r
End With

Set ws = Nothing
End Sub

Ciao,
Holger

[GIS2013]

Cheers! always great to learn from you