If value already exsists in range....

[Biff]

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

 

[Rowan Drummond]

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]

Hi Rowan!

Yes, everything is in the same wb.

Dim cpyVal As Variant 'I don't know the datatype of your data

Data type is numeric.

It's not working as I had hoped. It's copying the value and to the correct
location but it's still copying if the value already exsists.

Example:

copyrange = 1100

K2 = 927
K3 = 1024
K4 = 1100

1100 already exsists in K2:Kn. The routine is still copying 1100 to K5.

If the copyrange value already exsists in K2:Kn, then Msg box and end
routine. Otherwise, continue.

Thanks

Biff

 

[Biff]

Ooops!

Sorry, I was running the wrong module!

It works just fine. Thank very much you!

Biff

 

[Rowan Drummond]

Hi Biff

I started with a clean sheet. Typed 927 in K2, 1024 in K3 and 1100 in
K4. Typed 1100 in copyrange and ran macro. Got the message "Already
exists in K4".

Could your values in col K or copyrange include decimal places not shown
due to calculations and formatting?

Regards
Rowan

 

[Rowan Drummond]

You're welcome. Disregard my other response.

Regards
Rowan