avoiding duplicates
 

Dim sVal as Variant, rng as Range, rng2 as Range
Dim cnt as long

With worksheets ("Deals")
Set rng = .cells(Rows.Count,1).End(x1up) (2)
Set rng2 =.Range(Cells(1,1), rng(0))
End With

sVal = Worksheets("Sheet1").Range("Customer").Value
cnt = Application.Countif(rng2,sVal)
if cnt = 0 then
rng.Offset(0,0).Value = Worksheets("Sheet1").Range("Customer")
rng.Offset(0,1).Value = Worksheets("Sheet1").Range("Date")
End if

Assumes the Range("Customer") is a single cell.

Probably not exactly what you need, but you left out more than you told.

--
Regards,
Tom Ogilvy

"Jeremy" wrote in message
...
Hi, I have already asked this but I am still struglling with it.

I have a spreadsheet which has the cells in a row poplulated from cells in
a source sheet using this code:

With worksheets ("Deals")
Set rng = .cells(Rows.Count,1).End(x1up) (2)
End With

rng.Offset(0,0).Value = Worksheets("Sheet1").Range("Customer")
rng.Offset(0,1).Value = Worksheets("Sheet1").Range("Date")

etc for the rest of the cells in the row.

I need code to ensure that the content from the source sheet is not
already in a row in the target sheet. i.e avoid duplicates. if so an error
message is displayed and the operation cancelled.