Hi Biff

I have assumed that copyrange, Sheet1 and this macro are all in the same
workbook and that copyrange is a single cell. Let me know if any of
these are not correct:

Sub TryMe()
Dim eRow As Long
Dim cpyVal As Variant 'I don't know the datatype of your data
Dim fndRng As Range
eRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row
cpyVal = Range("copyrange").Value
With Sheets("Sheet1").Range("K2:K" & eRow)
Set fndRng = .Find(cpyVal)
End With
If Not fndRng Is Nothing Then
MsgBox "Already exist in cell " & fndRng.Address
Else
Sheets("Sheet1").Cells(eRow + 1, 11).Value = cpyVal
End If
End Sub

Regards
Rowan

Biff wrote:
Hi Folks!

I have this chunk of code (thanks to Tom Ogilvy) that I would like to tweak
for a different application.

Sub copyrange()
Dim rng As Range, rng1 As Range
Dim cell As Range, i As Long
Set rng1 = Workbooks("XXX.xls").Sheets("Sheet1") _
.Cells(Rows.Count, 11).End(xlUp)(2)
Set rng = Range("copyrange")

i = 0
For Each cell In rng
rng1.Offset(0, i).Value = cell
i = i + 1
Next

End Sub

It copies the value in a named cell, "copyrange", to the first empty cell
starting in Sheet1 K2.

What I'd like to tweak is that before the value is copied is to check and
make sure that value doesn't already exsist in the range K2:Kn. If the value
does not exsist continue with the copy. If the value does exsist pop a msg
box then end the routine.

Any help would be greatly appreciated.

Biff