• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check for existing numbers in colom with VBA

Hello,
I have a colom (D) with empty spaces , I would like to have a message box if in the colom the value already exist but also if the value +1 exist when i fill in an number in one of the empty spaces
for example 1234 exists in colom D, then I should a messagebox like :Number 1234 and 1235 already exist
file included
thanks in advance
 

Attachments

Slightly different method.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim x, x2
lRow = Cells(Rows.Count, 4).End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

If Not Intersect(Target, Range("D:D")) Is Nothing Then
    With Range("D2:D" & lRow - 1)
        x = Evaluate("MATCH(" & Target.Address & "," & .Address & ",0)")
        x2 = Evaluate("MATCH(" & Target.Value + 1 & "," & .Address & ",0)")
        If IsNumeric(x) Or IsNumeric(x2) Then
            MsgBox "Code " & Target.Value & " or " & Target.Value + 1 & " already exists"
            Target.Select
            Target.ClearContents
        End If
    End With
End If
End Sub
 
Hello Chihiro,
Thank you for your quick response and great solution. I like the
Code:
Target.ClearContents
part.
Have a nice evening
 
Back
Top