VBA: if any cell in range matches in another range?

[JumboCactuar]

Hi,
I have some data we input into range C2:C20 and on button press the rows are pasted to the bottom of Sheet("Data")

What I'm wanting is if any of the values in C2:C20 are already in column C in the data sheet to display an error message Yes/No

Appreciate any help

 

[BarryL]

you can make a dictionary of all the data in the data sheet column C then check if the new data exists on that sheet.

Something like the below (untested), but you should add in the sheet name where your new input data is.

Sub test()
Dim dic As Object, rng As Range, a As Variant, b
Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("Data").Range("C2:C" & Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(a)
dic.Add a(i, 1), 1
Next


For Each rng In Range("C2:C20")
If dic.exists(rng.Value) Then
 b = MsgBox(rng.Value & " exists in Data sheet. Do you wish to proceed adding it?", vbYesNo)
If b = vbYes Then
Sheets("Data").Range("C" & Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row + 1).Value = rng.Value
End If
Else
Sheets("Data").Range("C" & Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row + 1).Value = rng.Value
End If
Next
End Sub

 

[JumboCactuar]

Thank you looks good, I will try this out later