Clear a cell if duplicate VBA

[Caveman1964]

Please forgive my stupidity......I am trying to put forth the effort.
Ok, I got this code to find a duplicate on another page, but what I have written doesnt work to clear the cell after it learned it was a duplicate.

hear is my novice code.

Sub askcompnum()


Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
Range("E5") = Title


If WorksheetFunction.CountIf(Worksheets("Data Collection").Columns(1), Worksheets("Complaint Entry").Range("E5")) Then
MsgBox "Duplicate, You cannot use"

If MsgBox = "Duplicate, you cannot use" Then
Range("E5").Select
ActiveCell.Clear

Else

End If
End Sub

 

[steve the fish]

try this:

Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")

If IsNumeric(Title) Then
    mtch = Application.Match(Title, Worksheets("Data Collection").Columns(1), 0)
    If IsError(mtch) Then mtch = Application.Match(CDbl(Title), Worksheets("Data Collection").Columns(1), 0)
Else
    mtch = Application.Match(Title, Worksheets("Data Collection").Columns(1), 0)
End If

If Not IsError(mtch) Then
    MsgBox "Duplicate, You cannot use"
    Worksheets("Complaint Entry").Range("E5").Value = vbNullString
Else
    Worksheets("Complaint Entry").Range("E5").Value = Title
End If

 

[rlv01]

Sub askcompnum()
    Dim Title
    Dim Duplicate As Boolean


    Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
    Range("E5").Value = Title


    Duplicate = (WorksheetFunction.CountIf(Worksheets("Data Collection").Columns(1), Worksheets("Complaint Entry").Range("E5")) > 0)
    If Duplicate Then
        MsgBox Range("E5") & " is duplicate LSS complaint tracking number" & vbNewLine & vbNewLine & "You cannot use it", vbExclamation, "Data Validation Check"
        Range("E5").ClearContents
    End If
End Sub

 

[Trebor76]

Hi there,

Try this where the entry in the inputbox won't be returned to cell E5 of the Complaint Entry tab if it already exists in column A of the Data Collection tab:

Option Explicit
Sub Macro1()

    Dim varResponse As Variant
    
    varResponse = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
    
    If varResponse <> "" Then 'Will = "" if the <Cancel> button was clicked
        If WorksheetFunction.CountIf(Worksheets("Data Collection").Columns("A"), varResponse) >= 1 Then
            MsgBox varResponse & " already exists and therefore cannot be used again.", vbExclamation
        Else
            Sheets("Complaint Entry").Range("E5").Value = varResponse
        End If
    End If

End Sub

Regards,

Robert

 

[Caveman1964]

This worked PERFECT!
Thank You!

 

[Caveman1964]

Thank You! Much appreciated!